1

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?

2

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`
;
  • Thanks, returns the literal result I had in the question, but not quite what I meant. I have updated the question. The result should really only have one column and a row for each matching order id. I was hoping to avoid group concat but maybe it is necessary. – Magnus Nov 11 '16 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.