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

Hello

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

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.

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 :slight_smile:

SELECT new_npt.*
  FROM new_npt 
LEFT OUTER
  JOIN aajos_chronoforms_register 
    ON aajos_chronoforms_register.npt_val = new_npt.npt_code
   [COLOR="Blue"]AND [/COLOR]aajos_chronoforms_register.uid = 63
 WHERE aajos_chronoforms_register.npt_val IS NULL 

Well that would explain it then!

I read that page and came up with:

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.

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/en/rewriting-subqueries.html