Mysql – Find records with the same relationships

MySQL

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?

Best Answer

One way to achieve that would be using Group_Concat() to stuff all the products for each order into one line, then match up all of the orders that have the same products and use Group_Concat() again to stuff all of them into the same line. See example:

Select `cte`.`Order` As `Queried order`, 
    Group_Concat(`cte2`.`Order` Order By `cte2`.`Order`) As `Returned Orders`
  From (
    Select `Order`, 
        Group_Concat(`Product` Order By `Product` Asc) As `Products`
      From `Table1`
      Group By `Order`) As `cte`
  Join (
    Select `Order`, 
        Group_Concat(`Product` Order By `Product` Asc) As `Products`
      From `Table1`
      Group By `Order`) As `cte2` On `cte`.`Products` = `cte2`.`Products`
  Group By `cte`.`Order`
;

This may be ugly, but it doesn't require multiple steps, or knowing the number of products up front.

Edit Should be easier to get the output that you now describe. Just remove the final GROUP BY and GROUP_CONCAT:

Select `cte`.`Order` As `Queried order`, 
    `cte2`.`Order` As `Returned Order`
  From (
    Select `Order`, 
        Group_Concat(`Product` Order By `Product` Asc) As `Products`
      From `Table1`
      Group By `Order`) As `cte`
  Join (
    Select `Order`, 
        Group_Concat(`Product` Order By `Product` Asc) As `Products`
      From `Table1`
      Group By `Order`) As `cte2` On `cte`.`Products` = `cte2`.`Products`
;
Related Question