[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query syntax to select ALL minus results from another query

Posted on 2014-08-11
7
Medium Priority
?
242 Views
Last Modified: 2014-08-11
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' or users.Usertype= 'paralegal') and archivedcont = 0

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' or users.Usertype= 'paralegal')
0
Comment
Question by:Aleks
  • 3
  • 3
7 Comments
 
LVL 11

Expert Comment

by:Ray
ID: 40253644
So you want all users NOT linked to a case, correct?
0
 
LVL 11

Accepted Solution

by:
Ray earned 2000 total points
ID: 40253652
If my earlier question was correct, this code should do it (although it may not be the most efficient way).

Basically, it just takes your list of all users, then says where the user is not in query2.  That leaves the rest of the users.

SELECT *
FROM dbo.Users
WHERE (users.Usertype = 'attorney' OR users.Usertype = 'paralegal') AND archivedcont = 0 AND users.userid NOT IN (
            SELECT users.userid
            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' OR users.Usertype = 'paralegal'
                        )
            )
0
 

Author Comment

by:Aleks
ID: 40253749
Trying now ..
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Aleks
ID: 40253757
Awesome !!!  Thank you !
0
 
LVL 32

Expert Comment

by:awking00
ID: 40253783
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);
0
 

Author Closing Comment

by:Aleks
ID: 40253864
Sorry forgot to assign the points
0
 
LVL 11

Expert Comment

by:Ray
ID: 40254041
Glad I could help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

CTAs encourage people to do something specific to show interest in your company, product or service. Keep reading to learn why CTAs should always be thought of as extremely important, albeit small, sections of websites.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question