sql select orders with similar items

I need to select those orders in pairs who have the same products in them. ORDER_ITEMS contain the product and a foreign key to reference the parent ORDER row. Order rows need to be different.

I've managed to list out pairs with count how many matching products they have in them, but that's only a similarity count. I need to exclude orders from pairs who have different products in them.

Can have Oracle specif开发者_JAVA技巧ic stuff in it.

The two tables are:

Order(order_id, customer_id...) 
Order_Item(item_id, order_id FK, product_id,...)

I need tose order_id-s that have all Order_Item childs with matching product_id-s.

Ex. in Orders

{ (ord1, cust1)  
  (ord2, cust2)}  

and in Order_Items

{ (item1, ord1, product_id=3),  
  (item2, ord1, product_id=6),   
  (item3, ord2, product_id=3),   
  (item4, ord2, product_id=6) } 

So basically, two people bought exactly the same two things. They are a pair. Those orders whose ordered products don't match exactly are not listed.

---

You haven't specified db version so I'm assuming 11g - not tested, but I think it will give you the general idea:

SELECT * FROM (
  WITH qry AS (
    SELECT DISTINCT
           order_id
          ,LISTAGG(product_id,'+')
           WITHIN GROUP (ORDER BY product_id)
           AS order_signature
    FROM   order_items
    GROUP BY order_id)
  SELECT order_id
        ,order_signature
        ,COUNT(DISTINCT order_id)
         OVER (PARTITION BY order_signature)
         count_same
  FROM   qry
) WHERE count_same > 1;

Limitation: it won't work if some orders are very big, e.g. 100s or 1000s of product IDs.

---

I'm not sure what your final data set needs to look like, but selecting from Customers with an EXISTS expression in the WHERE clause to look for order matches will get you there.