0
CREATE TABLE orders (
    Order_ID int,
    Product_ID int,
    PRIMARY KEY(Order_ID,Product_ID)
);

INSERT INTO orders VALUES (1,1), (1,2), (1,3), (2,2), (2,3), (3,3), (3,4);

Each order has a set of products; how do I get all orders which have the product set of the order 2 as a subset of their product sets?

I want to get as results

OrderID
--
 1
 2

For further understanding:

In the example: The order 2 has products {2,3}

The result must be orders 1 and 2, because the order 1 has products {1,2,3} — it has subset {2,3}. On the other hand, the order 3's product set is {3,4} — it doesn't have subset {2,3}, so it must not returned in the result.

Joker_vD
  • 3,715
  • 1
  • 28
  • 42
Emka
  • 340
  • 6
  • 16
  • I made that question, too. By the time i refreshed it, was already closed so I made one new – Emka Apr 22 '13 at 11:10
  • I know you did. But your new question doesn't contain any more information than the old one - why do you think the old one was close? And you again didn't bother to format it properly. –  Apr 22 '13 at 11:11
  • the old question did not contained the last 7 lines. I changed the question after it was closed so the refresehd question had no use. – Emka Apr 22 '13 at 11:18
  • But the *actual* question which Inflane was trying to ask is pretty reasonable. – Joker_vD Apr 22 '13 at 11:41
  • @Inflane : your question have been reopened. I've tried to merge the two for a better understanding, and I answer too. – Cyril Gandon Apr 22 '13 at 13:55

2 Answers2

2
SELECT DISTINCT Order_ID 
FROM Orders T1 
WHERE NOT EXISTS (
    SELECT Product_ID 
    FROM Orders 
    WHERE Order_ID = 2
  EXCEPT
    SELECT Product_ID 
    FROM Orders 
    WHERE Order_ID = T1.Order_ID);

Okay, that's probably not the most efficient way, but hey! The DBMS will have to look at every row anyway.

Joker_vD
  • 3,715
  • 1
  • 28
  • 42
0

It should be something like this I think

select distinct orderid
from table_name
where product_id in ALL (select product_id from table_name where orderid = 2)
NielsC
  • 344
  • 1
  • 8