5

I have two tables with the same schema:

dbo.orders_1
| user_id | order_id | create_time             |
|---------|----------|-------------------------|
| 1       | 5        | 2018-05-04 02:26:03.808 |
| 1       | 6        | 2021-01-01 02:26:03.821 |
| 2       | 12       | 2021-12-12 02:26:03.814 |

dbo.orders_2
| user_id | order_id | create_time             |
|---------|----------|-------------------------|
| 6       | 7        | 2020-06-04 02:26:03.808 |
| 1       | 5        | 2021-01-01 02:26:03.821 |
| 3       | 4        | 2021-12-12 02:26:03.814 |

This is how I can find duplicated rows between two tables:

select 
  a.user_id, 
  a.order_id, 
  a.create_time as "create_time_1", 
  b.create_time as "create_time_2"
from 
  orders_1 a 
  join orders_2 b on a.user_id = b.user_id 
  and a.order_id = b.order_id

Which gives me this output:

| user_id | order_id | create_time_1           | create_time_2           |
|---------|----------|-------------------------|-------------------------|
| 1       | 5        | 2020-06-04 02:26:03.808 | 2021-01-01 02:26:03.821 |

It works, but now I added another table:

dbo.orders_3
| user_id | order_id | create_time             |
|---------|----------|-------------------------|
| 77      | 2        | 2015-09-15 02:26:03.808 |
| 3       | 4        | 2018-04-15 02:26:03.814 |

I want to find any duplicates in those 3 tables. What I expect is:

| user_id | order_id | create_time_1           | create_time_2           | create_time_3           |
|---------|----------|-------------------------|-------------------------|-------------------------|
| 1       | 5        | 2020-06-04 02:26:03.808 | 2021-01-01 02:26:03.821 | NULL                    |
| 3       | 4        | NULL                    | 2021-12-12 02:26:03.814 | 2018-04-15 02:26:03.814 |

What should sql look like? This is my online playground: https://sqlize.online/sql/mssql2019/7f31a47ef4c633ad8b4ba88a4276964d/

4
  • How do you define a duplicate? Only the same user_id and order_id or create_time as well?
    – Zikato
    Sep 30, 2022 at 8:26
  • only the same user_id and order_id is a duplicate in this case. create_time doesn't matter
    – dafie
    Sep 30, 2022 at 8:28
  • And do you want that specific output? Or do you just want to detect duplicates along with the detail?
    – Zikato
    Sep 30, 2022 at 8:34
  • I just thought about that specific output, but different one should be good as long as it gives information about which rows are "duplicated".
    – dafie
    Sep 30, 2022 at 8:37

2 Answers 2

1

Usually, when finding the duplicate rows in two tables, I use a set operator INTERSECT, but that would require all the tables to contain the duplicate row.

To find the dupes in any combination of two tables, I would use UNION ALL to bring all the rows (including duplicates) into a single table expression and then find them.

Then you can use aggregation to find the duplicates, but you will lose the detail (create_time). Or use window functions to detect the dupes and keep the detail.

I've edited your playground code here.

; -- Previous statement must be properly terminated
WITH allTables
AS
(
    SELECT 'orders_1' as src, * FROM dbo.orders_1 UNION ALL
    SELECT 'orders_2' as src, * FROM dbo.orders_2 UNION ALL
    SELECT 'orders_3' as src, * FROM dbo.orders_3
)
, detectDupes
AS
(
    SELECT 
        *
        , COUNT(1) OVER (PARTITION BY t.user_id, t.order_id) AS dupeCount
    FROM allTables AS t
)
SELECT 
    *
FROM detectDupes
WHERE detectDupes.dupeCount > 1
ORDER BY 
    user_id
    , order_id
    , src

Which gives me this output

enter image description here

0

Another way you can accomplish your goal is just FULL JOINing all of the Tables involved, and using a WHERE clause to filter out anything that isn't a duplicate like so:

SELECT
    COALESCE(a.user_id, b.user_id, c.user_id) AS user_id,
    COALESCE(a.order_id, b.order_id, c.order_id) AS order_id,
    a.create_time AS create_time1,
    b.create_time AS create_time2,
    ISNULL(c.create_time, d.create_time) AS create_time3
FROM dbo.orders_1 AS a
FULL JOIN dbo.orders_2 AS b
    ON a.user_id = b.user_id
    AND a.order_id = b.order_id
FULL JOIN dbo.orders_3 AS c
    ON a.user_id = c.user_id
    AND a.order_id = c.order_id
FULL JOIN dbo.orders_3 AS d
    ON b.user_id = d.user_id
    AND b.order_id = d.order_id
WHERE
    (a.user_id IS NOT NULL AND b.user_id IS NOT NULL) -- A duplicate between orders_1 and orders_2
    OR (a.user_id IS NOT NULL AND c.user_id IS NOT NULL) -- A duplicate between orders_1 and orders_3
    OR (b.user_id IS NOT NULL AND d.user_id IS NOT NULL) -- A duplicate between orders_2 and orders_3

Notice there are 3 FULL JOINs, one for each Table comparison, i.e.: orders_1 to orders_2, orders_1 to orders_3 and orders_2 to orders_3.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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