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

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

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