[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

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

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
Dovberman
Asked:
Dovberman
2 Solutions
 
aaronakinCommented:
SELECT *
  FROM Customers
  WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders)
0
 
chapmandewCommented:
or like this

select * from customers c where not exists(select 1 from orders o where o.customerid = c.customerid)
0
 
adlink_laCommented:
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
 
DovbermanAuthor Commented:
aaronikin and chapmandew split the points. Both returned 38 rows. adlink la returned 32 rows. Thanks to all.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now