SQL match data from 2 tables

Question

I've got 2 tables.

'Order details'
OrderID | Quantity | UnitPrice | ProductID
  1002  |    19    |    17     |    824
  1002  |    5     |    15     |    285
  1003  |    7     |    17     |    824
  1003  |    7     |    15     |    285
  1003  |    7     |    11     |    205
  1004  |    12    |    11     |    205


'Orders'
OrderID | CustomerID
  1002  |   224
  1003  |   348
  1004  |   224

I need to find CustomerIDs that have the same orders (ProductID) as another CustomerID, for instance ID number 224. All the orders must be taken, I mean all its OrderIDs. So the output would be 348, as this id has absolutely the same productids in his orders.

Can you clarify? Customer 224 has two orders associated, is this what you want to find? Can you specify the output you expect from the example data you posted? — Massimiliano Carosi, Jul 23 '16 at 14:15
Possible duplicate of [SQL find sets with common members (relational division)](http://stackoverflow.com/questions/16314527/sql-find-sets-with-common-members-relational-division) — Serg, Jul 23 '16 at 14:19
@MassimilianoCarosi updated the question, hope it is more clear :) — Oleksii Kryzh, Jul 23 '16 at 14:20
@GordonLinoff it is important that they have absolutely the same products. If you mean the quantity column, it is not important — Oleksii Kryzh, Jul 23 '16 at 14:57

Gordon Linoff · Answer 1 · 2016-07-24T14:05:50.903

1

If you want customers with the same products:

with od1 as (
      select distinct o.customerid, od.productid
      from orderdetails od join
           orders o
           on o.orderid = od.orderid
     ),
     od as (
      select od1.*,
             (select count(*) from od1 od2 where od2.customerid = od1.customerid) as numproducts
      from od1
     )
select od.customerid
from od od join
     od od2
     on od.productid = od2.productid and od.numproducts = od2.numproducts and
        od.customerid = 224
group by od.customerid
having count(*) = od.numproducts;

The purpose of the CTE is simply to get one row per customer, product, and the count of products. The outer query counts the number of matches between two customers. The number of matches needs to match the number of products.

This returns exact matches. The second customer has to have exactly the same products, no more, no less.

Note: This returns the original customer (easily filtered out with a where clause).

edited Jul 24 '16 at 14:05

answered Jul 23 '16 at 15:00

Gordon Linoff

1,242,037
58
646
786

Thank you very much, unfortunately, I have an error '''circular reference: od', – Oleksii Kryzh Jul 23 '16 at 15:15
I will split in two left join later. – Massimiliano Carosi Jul 23 '16 at 16:51
@MassimilianoCarosi ok, I wil wait – Oleksii Kryzh Jul 24 '16 at 09:55
@OleksiiKryzh . . . I fixed the problem; I just used the wrong alias in the CTE. – Gordon Linoff Jul 24 '16 at 13:11
@GordonLinoff sorry, but "no such column: od.customerid", There is no column customerID in 'Order details' table – Oleksii Kryzh Jul 24 '16 at 13:36
@OleksiiKryzh . . . There should be. `od1` has `customerid`. All columns from `od1` are in `od`, so it should be there as well. I did just fix a typo, which might be related to the issue. – Gordon Linoff Jul 24 '16 at 14:06

Massimiliano Carosi · Answer 2 · 2016-07-24T15:35:06.587

In the following sketch I list all the orders that has exactly the same products of some order made by the Customer Id 224

select distinct o2.OrderId
from    
    Orders o1, Orders o2
where 
    o1.CustomerId = 224
    and o1.OrderId <> o2.OrderId
    and not exists (
        select 1 
        from 
            "Orders details" od1 
            full outer join "Order details" od2 on 
                od1.OrderId = o1.OrderId 
                and od2.OrderId = o2.OrderId 
                and od1.ProductId = od2.ProductId
        where
            od1.OrderId is null or od2.OrderId is null
    )

the rest should be easy.

If your DBMS does not support full outer join, you can split it into two left join like this:

select distinct o2.OrderId
from    
    Orders o1, Orders o2
where 
    o1.CustomerId = 224
    and o1.OrderId <> o2.OrderId
    and not exists (
        select 1 
        from 
            "Orders details" od1 
            left join "Order details" od2 on 
                od1.OrderId = o1.OrderId 
                and od2.OrderId = o2.OrderId 
                and od1.ProductId = od2.ProductId
        where
            od2.OrderId is null
    )
    and not exists (
        select 1 
        from 
            "Orders details" od1 
            left join "Order details" od2 on 
                od1.OrderId = o2.OrderId 
                and od2.OrderId = o1.OrderId 
                and od1.ProductId = od2.ProductId
        where
            od2.OrderId is null
    )

Thank you very much, but I have an error 'RIGHT and FULL OUTER JOINs are not currently supported'. Maybe there are any other ways to solve this? — Oleksii Kryzh, Jul 23 '16 at 15:17

paparazzo · Answer 3 · 2016-07-23T14:42:02.847

0

with cte1 as 
(  select CustomerID, ProductID 
     from order 
     join detail
       on order.OrderID  = detail.OrderID )
, cte2 as 
(  select CustomerID, count(*) as cnt 
   from cte1 
   group by CustomerID
)
select cte2a.CustomerID, cte2b.CustomerID
  from cte2 as cte2a
  join cte2 as cte2b
    on cte2a.CustomerID < cte2a.CustomerID
   and cte2a.cnt< cte2a.cnt
  join cte1 as cte1a
    on cte1a.CustomerID = cte2a.CustomerID
  join cte1 as cte1b
    on cte1b.CustomerID = cte2b.CustomerID
   and cte1a.ProductID  = cte1b.ProductID 
 group by cte2a.CustomerID, cte2b.CustomerID, cte2a.cnt
 having count(*) = cte2a.cnt

edited Jul 23 '16 at 14:42

answered Jul 23 '16 at 14:35

paparazzo

44,497
23
105
176

But where should I put customerid which I want to be compared to others? – Oleksii Kryzh Jul 23 '16 at 15:33
This should give you all customerID pairs that match – paparazzo Jul 23 '16 at 15:35

SQL match data from 2 tables

3 Answers3