Solved

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

Posted on 2008-09-30
4
245 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Varible Table 3 24
How to search for strings inside db views 4 27
Query group by data in SQL Server - cursor? 3 34
T-SQL:  Collapsing 9 25
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

778 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