help with backwards compatibility - v5 to v4

[jez]

Hi everyone,

I am trying to find all the values in one column of a table that do not appear in another column of the same table.

On my local machine running mysql v5 the following does nicely;-

SELECT cat.id, cat.parentid FROM categories cat WHERE cat.id NOT IN (SELECT distinct parentid FROM categories);

Unfortunately the development environment (unlike anywhere else in the whole place) is running mysql v4.1 where sub queries dont work.

So... i have put this together to try and acheive the same thing;-

SELECT cat.id, cat.parentid
FROM categories cat
LEFT JOIN (
 SELECT DISTINCT (cat2.parentid) AS cat2parentid FROM
 categories cat2
) as uniParent
ON cat.id not in (uniParent.cat2parentid);

This is giving me a Cartesian product i.e. thousands of results, whereas i get about 300 from the v5 query.

Any suggestions welcome.

Thanks

 

[piti]

try
ON cat.id = uniParent.cat2parentid
WHERE uniParent.cat2parentid IS NULL

should work, I hope ;-)

 

[jez]

I'm afraid that brings back an empty result set, but i think you are on the right track and i will try some variations on that.

Thanks very much!

 

[piti]

well ... have you tried? when i look at your code (the one with join) and replace the join condition with my part ... i'm almost sure it would not return an empty set

 

[jez]

No you were right, I had a typo in my sql.
It does work as you typed it, thanks very much for the help.