Say I have Orders and Products.
For a given Order I want to find all the other orders that are made up of the exact same products.
So the schema would look something like this:
CREATE TABLE Order ( id int ... ) CREATE TABLE Product ( id int, name text ) CREATE TABLE OrderProduct ( OrderId int, ProductId int )
Consider the following data.
Order| Product 1|Burger 1|Drink 1|Fries 2|Burger 2|Drink 3|Burger 4|Burger 5|Burger 5|Drink 5|Fries
The desired query would return a list of every order that has the exact same products:
Where order ID -> Returned Result rows 1-> 1 5 2-> 2 3-> 3 4 4-> 3 4 5-> 1 5
Everything I have come up with so far requires ugly queries, multiple steps and knowing how many products will belong to an order up front.
Is there a simple way to achieve this?