a simple(?) join of 2 tables

[bartcha]

Hi!

OK, somehow I got a blackout trying to formulate an answer for this question...I have 2 tables (actually many more, but for this they are irrelevant), say

- customer
- order

the tables are joined by customerID. Within the order-table I use itemID to refer to the item bought.

How would I select all the customers that have bought 2 certain items, ie. have an order with itemID=100 and also have an order with itemID=200?

Hope this is clear enough?!

 

[JarlH]

Check out GROUP BY with HAVING.

 

[bartcha]

Thanks but could you give me an example?

Select Customer.*
From Customer, order
Where customer.customerID=order.customerID

...?

 

[JarlH]

Another approach:

SELECT * FROM customer
WHERE (SELECT COUNT(DISTINCT itemid)
FROM order
WHERE order.customerid = customer.customerid
AND itemid IN (100,200)) = 2


Core SQL-2003!


Note that ORDER is a reserved word in SQL, so you'd better double quote it, like "ORDER", or change it.

 

[bartcha]

Thnks again, but I get an syntax-error. As i'm not sure how the actual syntax should look...?

what I tried is the exact code you provided in the example...any last tweaks?!

Bart

 

[JarlH]

My statement above conforms to the ANSI/ISO SQL standard. (After I've changed order to "ORDER"...)

What error message do you get? Which DBMS are you using?

 

[JarlH]

A somewhat silly solution:

SELECT * FROM customer
WHERE customerid in
(SELECT customerid
FROM "ORDER" o1, "ORDER" o2
WHERE o1.customerid = o2.customerid
AND o1.itemid = 100 AND o2.itemid = 200);

 

[bartcha]

I'm using MS Access during development so I guess there are some differences between Ansi and the MS Jet engine....

I found the solution using Group BY, although it will be a tough quest to integrate it with the rest of my (quite extensive) query...

SELECT c.CustomerID
FROM Customer c,
(SELECT DISTINCT CustomerID,ItemID
FROM Orders
WHERE ItemID IN (100,200)) d
WHERE c.CustomerID = d.CustomerID
GROUP BY c.CustomerID
HAVING COUNT(*) = 2

I liked your solution better where everything is in the Where-section (as I'm using Cold Fusion for all kind of dynamic variables and exceptions and that will be easier to integrate).

Any last ideas, otherwise I'll go and integrate the above solution.

thanks again!

 

[PHV]

And what about this ?
SELECT DISTINCT c.CustomerID
FROM (Customer C
INNER JOIN Orders A ON C.CustomerID = A.CustomerID)
INNER JOIN Orders B ON C.CustomerID = B.CustomerID
WHERE A.ItemID = 100 AND B.ItemID = 200

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

I guess there are some differences between Ansi and the MS Jet engine....

quote of the week


but it's still early


r937.com | rudy.ca

 

[bartcha]