Using 'NOT EXISTS' in MySQL

[mluken]

My SQL knowledge is not great, but this query below worked in Access and I have not been able to get it to work with MySQL. Basically I want to pull all the rows in table1 that do not have corresponding rows in table2:

SELECT T1.USR_ID,
T1.CO_NM,
T1.FRST_NM,
T1.LST_NM
FROM DB2_USRS T1
WHERE NOT EXISTS (
SELECT ' ' FROM DB2_CLIENT_DM T2
WHERE T1.USR_ID = T2.USR_ID
)
ORDER BY T1.CO_NM, T1.LST_NM

Anything obvious that anybody can see why this would work in Access and not MySQL?

 

[TonyGroves]

You can't use sub-selects in MySQL until version 4.1 (currently alpha).
However, the following should work instead:
[tt]
SELECT t1.usr_id,t1.co_nm,t1.frst_nm,t1.lst_nm
FROM
db2_usrs t1 LEFT JOIN db2_client_dm t2 USING (usr_id)
WHERE t2.usr_id IS NULL
ORDER BY t1.co_nm,t1.lst_nm
[/tt]

-----
ALTER world DROP injustice, ADD peace;

 

[mluken]

Do you know if I can do sub-selects in Microsoft's SQL Server 2000?

 

[bastienk]

yes mssql supports subselects


Bastien

Cat, the other other white meat

 

[sleipnir214]

mluken:
You know, Tek-Tips has 4 or 5 entire fora dedicated to MSSQL.



Want the best answers? Ask the best questions!

TANSTAAFL!!