Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Nov 2002
    Location
    Loughborough (UK)
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MySQL v4 - WHERE NOT IN?

    Hi all,

    I might be approaching this in the wrong way, but I want to run a SQL statement in MySQL v4, that brings back rows from one table, where the parentID doesn't exist in another table. In SQL-Server, I would have just the WHERE syntax; " x NOT IN (SELECT y FROM z) " but this doesn't seem to work.

    What's the best way to achieve this in MySQL v4?

    Thanks,
    Chris

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    MySQL version 4.0 doesn't do subselects but 4.1 does.

    To do this type of thing in 4.0 you can use a LEFT JOIN, which will select all rows in the "A" table whether there is a row in the "B" table or not. For those "A" rows that do not have a "B" row, the "B" result will be NULL so you can put that in your "where" clause.

    Code:
    SELECT table1.*
    FROM table1
    LEFT JOIN table2 ON table1.id=table2.id
    WHERE table2.id IS NULL

  • #3
    Regular Coder
    Join Date
    Nov 2002
    Location
    Loughborough (UK)
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thank you!

    Why didn't I think of that! Nice one matey! Thanks


  •  

    Posting Permissions

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