View Full Version : MySQL v4 - WHERE NOT IN?

02-28-2007, 09:01 PM
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?


02-28-2007, 11:55 PM
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.

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

03-01-2007, 10:41 AM
Why didn't I think of that! Nice one matey! Thanks :thumbsup: