SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Mar 2005
    Location
    UK
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with nested select not in(.....)

    Hello

    Any ideas why this following sql works on MySQL 5 and not 3? :

    Code:
    SELECT DISTINCTROW new_npt.npt_name, new_npt.npt_code
    FROM new_npt
    WHERE new_npt.npt_code NOT 
    IN (
    SELECT DISTINCTROW new_npt.npt_code
    FROM new_npt
    LEFT  JOIN aajos_chronoforms_register  ON new_npt.npt_code = aajos_chronoforms_register.npt_val
    WHERE aajos_chronoforms_register.uid =63
    )
    ORDER  BY new_npt.npt_name
    This is really confusing me, any help or advise would be appreciated.

    Many thanks.

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,904
    Mentioned
    139 Post(s)
    Tagged
    2 Thread(s)
    MySQL 3 is not capable of handling subqueries.
    There is an article on the MySQL website how to rewrite such queries so that they work: http://dev.mysql.com/doc/refman/4.1/...ubqueries.html
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Member
    Join Date
    Mar 2005
    Location
    UK
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well that would explain it then!

    I read that page and came up with:

    Code:
    SELECT new_npt.*
    FROM new_npt LEFT JOIN aajos_chronoforms_register ON new_npt.npt_code = aajos_chronoforms_register.npt_val
    WHERE aajos_chronoforms_register.npt_val IS NULL;
    Im not sure where to put the where aajos_chronoforms_register.uid=63 bit, it can't go on the end where clause because that column only contains nulls.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT new_npt.*
      FROM new_npt 
    LEFT OUTER
      JOIN aajos_chronoforms_register 
        ON aajos_chronoforms_register.npt_val = new_npt.npt_code
       AND aajos_chronoforms_register.uid = 63
     WHERE aajos_chronoforms_register.npt_val IS NULL
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Mar 2005
    Location
    UK
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Lol you just beat me to my post, I tried that and it now works like a dream.

    Thanks all for your help with this


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •