What would be the SQL for finding the order numbers which have BOTH items 101 and 102?

Question

Given the following table, what would be the SQL for finding the order numbers which have BOTH items 101 and 102?

The orders table is structured Orders(orderno, itemno, quantity) with the following rows:

(O1, 101, 3)

(O2, 102, 5)

(O2, 101, 7)

(O2, 103, 5)

(O3, 101, 1)

(O4, 101, 2)

(O4, 102, 3)

Answer 1

SELECT orderno FROM orders

WHERE itemno = 102 AND orderno IN

(SELECT orderno FROM orders WHERE itemno = 101)

Answer 2

SELECT orderno FROM orders WHERE itemno=101 AND orderno IN (SELECT orderno FROM orders WHERE itemno=102)

Answer 3

Ok. You might want to tweak this a little bit, but I think it should fit the bill :

SELECT  orderno, Count(*) AS TotalOrders FROM

(SELECT orderno FROM Table1 WHERE itemno=101 OR itemno=102)

GROUP BY orderno

HAVING Count(*)>1;

Phewww!!! This one worked my brain a little bit - but, I'm a geek, so I like that! Thanks for the good Q.