AND Joins

[JFLund]

This is probably simple but I'll be darned if I can figure out how to do it...

I have a couple of tables linked by a CustomerID. The first table is of customers with the CustomerID field, the second is of items purchased by the customers. This Purchases table has a field for the ItemID and a CustomerID.

I want to create a query that will tell me which customers have purchased items with ItemID = 1 AND ItemID = 2 (and possibly Item = 3...) IE. I only want customers that have purchased BOTH or ALL the items, not just one of the items.

Any ideas?

Thanks
John F. Lund

[hpeter]

try sth like:

SELECT CustomerID
FROM tbl1 a, tbl2 b
WHERE a.CustomerID= b.CustomerID
HAVING count(*) >=2
GROUP BY a.CustomerID

[JBane]

John,

For 1 and 2:

select customerID from purchases t1 where
itemID = 1 and exists (select customerID from purchases t2
where t2.itemID = 2 and t1.customerID = t2.customerID)


For 1, 2, and 3:


select customerID from purchases t1 where
itemID = 1 and exists (select customerID from purchases t2
where t2.itemID = 2 and t1.customerID = t2.customerID)
and exists (select customerID from purchases t3
where t3.itemID = 3 and t1.customerID = t3.customerID)



Jeff