Finding customers who own both products...

  • Thread starter Thread starter junk
  • Start date Start date
J

junk

This is a simplified example of what is probably a simple SQL query,
but my SQL skills are too weak...

Customer table:
CustomerID
EmailAddress

Orders table:
CustomerID
ProductOrdered

There is a one-to-many relationship between Customers and Orders keyed
on the CustomerID field.

Multiple customers can have the same EmailAddress.

I want to find all unique Customer.EmailAddresses where the customer
has ordered both product A and B (i.e., where there is a related
Order.ProductOrdered="A" record and an Order.ProductOrdered="B"
record).

Similarly, I'll need to find all Customer.EmailAddresses where the
customer has ordered only product A and not product B (i.e., where
there is a related Order.ProductOrdered="A" record and there is not an
Order.ProductOrdered="B" record).

Thanks for your help!

Steve
 
Set up a query that returns the customers who ordered product A, and use a
subquery to see if they ordered product B as well.

This first example returns distinct email addresses for those customers who
ordered both products:

SELECT DISTINCT EmailAddress
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE Orders.ProductOrdered = "A"
AND EXISTS (SELECT CustomerID
FROM Orders AS Dupe
WHERE Dupe.CustomerID = Customer.CustomerID
AND Dupe.ProductOrdered = "B");

To return those who ordered A but not B, just add a NOT before the subquery,
i.e.:
AND NOT EXISTS (SELECT CustomerID

Note that this query is selecting *customers* based on their orders, and
then de-duping the email addresses. Thus if Customers 4 and 5 share an email
address, and Customer 4 ordered A but not B, and customer 5 ordered B but
not A, the query will not return this email address for the example query,
since neither customer ordered both products, even though both products were
ordered by someone with that email address.

If subqueries are new, Microsoft's introductory article:
How to Create and Use Subqueries
is at:
http://support.microsoft.com/?id=209066
 
Back
Top