Query syntax to select ALL minus results from another query
I have two queries. One will return 'all users' of a certain kind (usertype), the other will return a portion of those users that are linked to a 'case'.
The query I need is the results of query # 1, minus the results of query # 2, to avoid them for being duplicated when I display them on the screen.
Query # 1
SELECT *
FROM dbo.Users
WHERE (users.Usertype='attorney'
Query # 2
SELECT *
FROM dbo.Atts
INNER JOIN Users ON atts.userid = users.userid
WHERE CaseId = MMColParam and email IS NOT NULL and email <> '' and users.archivedcont = 0 and (users.Usertype='attorney'
The query I need is the results of query # 1, minus the results of query # 2, to avoid them for being duplicated when I display them on the screen.
Query # 1
SELECT *
FROM dbo.Users
WHERE (users.Usertype='attorney'
Query # 2
SELECT *
FROM dbo.Atts
INNER JOIN Users ON atts.userid = users.userid
WHERE CaseId = MMColParam and email IS NOT NULL and email <> '' and users.archivedcont = 0 and (users.Usertype='attorney'
Ray
So you want all users NOT linked to a case, correct?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Trying now ..
ASKER
Awesome !!! Thank you !
select * from dbo.users
where usertype in ('attorney','paralegal')
and archivedcont = 0
and not exists
(select 1 from dbo.atts
where caseid = MMColParam
and email is not null and email <> ''
and userid = users.userid);
where usertype in ('attorney','paralegal')
and archivedcont = 0
and not exists
(select 1 from dbo.atts
where caseid = MMColParam
and email is not null and email <> ''
and userid = users.userid);
ASKER
Sorry forgot to assign the points
Glad I could help.