Learn how to a build a cloud-first strategyRegister Now


SQL Select where doesnt (not) exist in another table?

Posted on 2009-04-25
Medium Priority
Last Modified: 2012-05-06
Another SQL question for you experts.

I have 2 tables:
tblContacts (ID, Name, etc)
tblContactGroupMapping(contactID, groupID)

I want to write a query that will select all the Contacts who are not assigned to at least 1 group in the tblContactGroupMapping table.

It seems like it should be easy with set operations, but i've never used those in SQL... or is there a better way?
Question by:BobBarker_99
LVL 25

Accepted Solution

reb73 earned 1600 total points
ID: 24232639
Try -
Select c.*
from tblContacts c
where not exists (select null from tblcontactgroupmapping where contactid = c.id)

Open in new window

LVL 41

Assisted Solution

ralmada earned 200 total points
ID: 24232754
Another alternative is using left join. Please check this out.

select a.* from tblContacts a
left join tblContactGroupMapping b on a.ID = b.contactID
where b.groupID is null

Open in new window


Assisted Solution

MeghanOBrien earned 200 total points
ID: 24233206

select * 
from tblContacts
where not exists (select * 
					from tblContactGroupMapping
					where tblContactGroupMapping.ContactID = tblContacts.ID)

Open in new window


Author Closing Comment

ID: 31574532
THanks.  THey all work great, and taught me a little more about sql.  

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

810 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