Results 1 to 3 of 3

Thread: AND Joins

Hybrid View

  1. #1
    Join Date
    Feb 2003
    Location
    Salt Lake City, UT
    Posts
    1

    AND Joins

    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

  2. #2
    Join Date
    Dec 2002
    Posts
    5
    try sth like:

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

  3. #3
    Join Date
    Dec 2002
    Posts
    181
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •