SUBQUERY HELP - tsql

[ecka00]

SELECT phs_COMPLIANCE_MONITOR.UID FROM phs_COMPLIANCE_MONITOR
WHERE phs_COMPLIANCE_MONITOR.UID NOT IN
(SELECT phs_COMPLIANCE_MONITOR2.UID FROM phs_COMPLIANCE_MONITOR2)

query works with "IN" only and there are def examples of UID's in phs_COMPLIANCE_MONITOR that are not in phs_COMPLIANCE_MONITOR2; HOWEVER I keep getting a NULL set when I add "NOT". PLEASE HELP

[skhanal]

Is there any NULL value in UID column?. Try filtering NULLs out.

SELECT phs_COMPLIANCE_MONITOR.UID FROM phs_COMPLIANCE_MONITOR
WHERE phs_COMPLIANCE_MONITOR.UID NOT IN
(SELECT phs_COMPLIANCE_MONITOR2.UID FROM phs_COMPLIANCE_MONITOR2
WHERE phs_COMPLIANCE_MONITOR2.UID IS NOT NULL)

[ecka00]

still not working. however, for some reason this does....

SELECT UID FROM phs_COMPLIANCE_MONITOR
WHERE NOT EXISTS
(SELECT UID FROM phs_COMPLIANCE_MONITOR2 WHERE phs_COMPLIANCE_MONITOR.UID = phs_COMPLIANCE_MONITOR2.UID)

thanks for the help. i'm not sure why the not exists works but i'll use it

[skhanal]

I suspect its NULL values, it can produce strange results. Are you using Oracle?

[ecka00]

nope--ms sql 2000