How I can find duplicate values in the result of a join operation?

Question

I have two tables

MappingTable > Id, ItemId, Quantity

ItemTable > ItemId, Name, DateOfPurchase

I wanted to find out the duplicate rows having same Quantity and same DateOfPurchase.

eg. I have 

Id ItemId Quantity
1  01      4
2  03      5
3  05      4

ItemId Name DateOfPurchase
01      AB    2019-10-30 18:30:00
05      XY    2019-10-30 18:17:00

Result:
Quantity DateOfPurchase Name
4        2019-10-30     AB 
4        2019-10-30     XY

So, I might join these tables and then find duplicates

How can I do that?

Please provide sample data, desired results, and explain what YOU mean by "duplicates". — Gordon Linoff, Apr 28 '20 at 22:15
Does this answer your question? [Finding duplicate values in MySQL](https://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql) — philipxy, Jun 11 '20 at 10:09

score 1 · Accepted Answer · answered Apr 28 '20 at 22:17

One option is to use window funtions, if your database supports them:

select *
from (
    select 
        m.*, 
        i.name, 
        i.dateOfPurchase,
        count(*) over(partition by m.quantity, p.dateOfPurchase) cnt
    from mapping m
    inner join item i on i.itemId = m.itemId
) t
where cnt > 1
order by quantity, dateOfPurchase

How I can find duplicate values in the result of a join operation?

1 Answers1