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

[RedCoupe]

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.

[ScallioXTX]

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/...ubqueries.html

[RedCoupe]

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.

[r937]

SELECT new_npt.*
  FROM new_npt 
LEFT OUTER
  JOIN aajos_chronoforms_register 
    ON aajos_chronoforms_register.npt_val = new_npt.npt_code
   AND aajos_chronoforms_register.uid = 63
 WHERE aajos_chronoforms_register.npt_val IS NULL

[RedCoupe]

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