I could use some help with this query - I seem to have come to a dead end here. I'm trying to find users that exist in one table but do not exist in another table. In the first table, user names are initials, like 'TDC'. In the second table, they can exist in two different formats. I want the query to return (ultimately) the number of users that exist in the first table but not the second.

If I run the following statement separately, I get results of 1 row. (User is TDC)

select count(*) from
(select distinct user_id from seat_assignment
where s_add_on_cd='...' );

When I run the following 2 statements separately, I get results that do not include user TDC:


select distinct upper(substr(osuser,10,8))
from v$session where
lower(program) <> 'oracle.exe' and
lower(program) <> 'jre.exe' and
lower(program) <> 'impserver.exe' and
lower(program) <> 'impadmin.exe' and
lower(program) <> 'cfserver.exe' and
lower(program) <> 'msaccess.exe' and
lower(program) <> 'sqlplusw.exe' and
lower(program) <> 'dbsnmp.exe' and
lower(program) <> 'jrew.exe';

select distinct upper(osuser) from v$session where
lower(program) <> 'oracle.exe' and
lower(program) <> 'jre.exe' and
lower(program) <> 'impserver.exe' and
lower(program) <> 'impadmin.exe' and
lower(program) <> 'cfserver.exe' and
lower(program) <> 'msaccess.exe' and
lower(program) <> 'sqlplusw.exe' and
lower(program) <> 'dbsnmp.exe' and
lower(program) <> 'jrew.exe';

However, when I combine all these statements together, I get a count of '0' when in fact I should get '1' for user TDC.


select count(*) from
(select distinct user_id from deltek.seat_assignment
where s_add_on_cd='...' and
user_id not in (select distinct upper(substr(osuser,10,8))
from v$session where
lower(program) <> 'oracle.exe' and
lower(program) <> 'jre.exe' and
lower(program) <> 'impserver.exe' and
lower(program) <> 'impadmin.exe' and
lower(program) <> 'cfserver.exe' and
lower(program) <> 'msaccess.exe' and
lower(program) <> 'sqlplusw.exe' and
lower(program) <> 'dbsnmp.exe' and
lower(program) <> 'jrew.exe') or
user_id not in
(select distinct upper(osuser) from v$session where
lower(program) <> 'oracle.exe' and
lower(program) <> 'jre.exe' and
lower(program) <> 'impserver.exe' and
lower(program) <> 'impadmin.exe' and
lower(program) <> 'cfserver.exe' and
lower(program) <> 'msaccess.exe' and
lower(program) <> 'sqlplusw.exe' and
lower(program) <> 'dbsnmp.exe' and
lower(program) <> 'jrew.exe'));

Does anyone have any ideas of why this is not working?

Dawn