syntax using NOT EXISTS

[mluken]

I have a query that worked when querying an access database but doesn't work when querying a MySQL database. I am basically trying to return all the rows from table1 that do not have a matching entry in table2:

SELECT T1.USR_ID,
T1.CO_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

I haven't done much SQL before so any thoughts here would be great! Why does this work in Access and not MySQL. Is there different syntax I need for MySQL?

Thanks!!!

 

[nigelrivett]

It should
better as

SELECT T1.USR_ID,
T1.CO_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

another way
SELECT T1.USR_ID,
T1.CO_NM
FROM DB2_USRS T1
left outer join DB2_CLIENT_DM T2
on T1.USR_ID = T2.USR_ID
where T2.USR_ID is null
ORDER BY T1.CO_NM


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[dinzana]

How about this one?


SELECT T1.USR_ID,
T1.CO_NM
FROM DB2_USRS T1
WHERE T1.USR_ID not in (SELECT DISTINCT T2.USR_ID
DB2_CLIENT_DM T2)
ORDER BY T1.CO_NM

 

[swampBoogie]

when querying a MySQL database.

You have posted in the wrong forum.

Mysql does not support subselects.

SELECT T1.USR_ID,
       T1.CO_NM
FROM DB2_USRS T1 left join
DB2_CLIENT_DM T2
on T1.USR_ID = T2.USR_ID
where t2.usr_id is null
ORDER BY T1.CO_NM