How to find duplicate combinations of columns across two tables in SQL?

Travis Callahan 0
Viewed 30

I am attempting to find duplicates across two tables which are identified as a combination of five different columns. The tables have one similar column in common. My table structure and relevant columns look like the following:

order_item_tbl

EVENT_KEY ORDER_KEY ORDER_NBR
14 82 1
14 82 2
14 82 3
14 82 1

invoice_tbl

EVENT_KEY CUSTOMER_KEY BOOTH_KEY
14 41 12
14 41 12

I've tried this query so far and everything is duplicated more than expected:

SELECT OI.ORDER_NBR AS ORDER_NBR, COUNT(*) AS COUNT
FROM ORDER_ITEM_TBL OI 
  JOIN INVOICE_TBL I
    ON I.EVENT_KEY = OI.EVENT_KEY
WHERE OI.EVENT_KEY = '14' AND OI.ORDER_KEY = '82'
AND I.BOOTH_KEY = '12' AND I.CUSTOMER_KEY = '41' AND OI.ORDER_NBR in (1,2,3)
GROUP BY OI.ORDER_NBR
HAVING COUNT(*) > 1

Based on this dataset I would expect to receive the following result:

ORDER_NBR COUNT
1 2

However this is the result I'm seeing:

ORDER_NBR COUNT
1 6
2 2
3 2

Any ideas on what I'm doing wrong here?

oracle sql
1 Answers

group by ORDER_NBR alone must give you the results i reckon as you have only ORDER_NBR in select .

The same query will not execute in posgres , as psql requires columns in group by to be in select or in aggregate funcations

sris skanda0
Related