Solved

Need to select all rows from table1 that do not have a matching key in table 2

Posted on 2008-09-30
4
237 Views
Last Modified: 2010-04-21
I have two tables ie Customers and Orders

I need to Select all customers for which there are no orders.

ie. Show the CustomerID for all orders where the CustomerID in the Customers table s not found in the CustomerID of the Orders table.
What is the correct SQL syntax ?
Thanks,
0
Comment
Question by:Dovberman
4 Comments
 
LVL 11

Accepted Solution

by:
aaronakin earned 125 total points
ID: 22605551
SELECT *
  FROM Customers
  WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders)
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 125 total points
ID: 22605560
or like this

select * from customers c where not exists(select 1 from orders o where o.customerid = c.customerid)
0
 
LVL 5

Expert Comment

by:adlink_la
ID: 22605771
If I read that right, you want customers that have no Orders?
SELECT CustomerID

FROM Customers

EXCEPT

SELECT CustomerID 

FROM Orders

Open in new window

0
 

Author Closing Comment

by:Dovberman
ID: 31501551
aaronikin and chapmandew split the points. Both returned 38 rows. adlink la returned 32 rows. Thanks to all.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Using Aggregate Functions to Count 3 34
Can someone plz fix this..getting an error 3 18
create insert script based on records in a table 4 15
Sql query 107 29
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now