February 1, 2008 at 1:12 pm
I have 2 tables:
Customers with: CustomerID (PK) and CustomerName
Orders: OrderID and CustomerID (FK)
I need to find all customers who didn't order anything from us, how do I go by doing that?
Thanks much
February 1, 2008 at 1:32 pm
I tried the following but it returns the customer that didn't order 3 times because my orders table has 3 rows of data:
select customers.customer_ID from customers,orders
where customers.Customer_ID not in (select orders.customer_ID from orders)
I tried using distinct and it only returned that customer who didn't order once:
select DISTINCT customers.customer_ID from customers,orders
where customers.Customer_ID not in (select orders.customer_ID from orders)
Is this the right way to do it?
Why were 3 records returned when not using DISTINCT?
Is there a better way to do this?
February 1, 2008 at 1:53 pm
Left join, to match all records and include the ones that don't have a match, then a filter to eliminate the rows that do have a match.
SELECT customerId
from customers LEFT OUTER JOIN -- retrieves rows ion customer that don't have matching rows in orders
Orders on Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderID is NULL -- where there is no matching row.
Re your 1st attempt
select customers.customer_ID from customers,orders
What you have there is a cross join. Two tables with no join criteria. What you'll get back from that is each row of customers, matched with each row of orders. If you have 2 customers and a total of 10 rows in orders, you'll get back 20 rows. It's called a cross join, and its a very quick way to get massive numbers of rows.
As an eg. Assume 2 table , T1 and T2.
T1 has the following 5 rows
a
b
c
d
e
T2 has the following 3 rows
1
2
3
4
If you do a cross join of T1 and T2 (SELECT * FROM T1, T2), you'll get this
a 1
a 2
a 3
a 4
b 1
b 2
b 3
b 4
c 1
c 2
c 3
c 4
d 1
d 2
d 3
d 4
e 1
e 2
e 3
e 4
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2008 at 2:04 pm
Thanks GilaMonster, you rock!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply