mysql "select not in"

[Bobby_S]

Hello

The "select not in" statement doesn't seem to work in mysql... Is there an equivalent?
I already came across the LEFT JOIN but I don't like such a solution.

Is there an easier way?

Ex.

SELECT email FROM tblusers
WHERE email NOT IN (
SELECT email FROM tblclients);

Thanks!

[chazzy]

what do you mean it doesn't seem to work? do you have an example as it seems to work just fine in my testing. what version do you use?

[Bobby_S]

Hi again, sorry for the late reply.
This is what I can't get working:

SELECT id, email_default
FROM lnk_partners
WHERE id >= '0'
AND id < '500'
AND statusid = '2'
AND email_default NOT
IN (

SELECT email
FROM tbl_participants
)
ORDER BY id ASC

MySQL said: Documentation
#1064 - You have an error in your SQL syntax near 'SELECT email FROM tbl_participants ) ORDER BY id ASC LIMIT 0, 30' at line 1

Any help?

[chazzy]

it's because of that extra line you got there. get read of the blank line between ( and the SELECT and it should be fine.

[Bobby_S]

hmm, no it's not that. The snippet there is a copy/paste of what phpmyadmin makes of it. I submit the sql in 1 line.

[chazzy]

ok, then again i ask you "what version do you use?"

[Bobby_S]

MYSQL versie 3.23.58

[chazzy]

well that's why.

you're using too old of a version. subqueries aren't supported until 4.1

[Bobby_S]

hmm damn.
Anyone has the lefty join solution?

I'll try on the first query:

SELECT u.email FROM tblusers u
LEFT JOIN tblclients c on c.email = u.email
;

That correct?

[Willio]

MySQL is free, so updating is free to. Seems to be a better solution then rebuilding the query.

[Bobby_S]

Aye, but I am not my own webhost...

[Willio]

Hmmz that's a small detail :-)

Then you don't have much choice...