Selecting products sold to a given set of customers

Question

I have two tables, product and sold_record. product contains my list of products, while sold_record contains all of my transactions. How can I select a distinct list of products that both customer A and customer B bought?

Answer 1

If your sold_record table has a product id and customer id, then the query would be:

select distinct s1.product_id 
 from sold_record as s1, sold_record as s2 
where s1.customer_id = "Customer A" 
  and s2.customer_id = "Customer B" 
  and s1.product_id = s2.product_id;

There may be a simpler way to do this using a join, but this should work.

Answer 2

Let's suppose table Product has a primary key ProdID, and table Sold a foreign key into Product by the same name as well as a Customer field (probably another foreign key into a third table Customer, but since you say there must be only two tables we'll assume a string that's the customer name, instead). Then:

Select DISTINCT Product.ProdID
From Product
Inner Join Sold SA On (Product.ProdID=SA.ProdID
                       AND SA.Customer='A')
Inner Join Sold SB On (Product.ProdID=SB.ProdID
                       AND SB.Customer='B')

will give you the ProdID in question (of course, you can get other columns from that table, too, we just have no idea what those columns might be;-).