Using NOT IN in a subquery 1

[amir4lamp]

I am using MySQL version 4.0.27-standard

everytime i use "not in" in the subquery:

select  ci.cat_id, ci.cat_title 
from    category_industry ci 
where   ci.cat_id not in 
                     (select mc.cat_id from members_categories mc where mc.member_id = 1);

it gives me an error message, whereas if i use 4.1.22-max it executes well. looking in the mysql manuals i have also tried using <> all, but that also does not work

is there a work around ??? your help is highly apprecited.

 

[TonyGroves]

MySQL 4.0 does not support subqueries.

For that query, you could instead use:
[tt]
SELECT ci.cat_id, ci.cat_title
FROM
category_industry ci
LEFT JOIN members_categories mc
ON ci.cat_id=mc.cat_id AND mc.member_id=1
WHERE mc.cat_id IS NULL
[/tt]