How to get all duplicates row in MySQL-CodePudding

I have a tables companies and addresses and need to get all duplicated rows

Checking columns is companies.phone_number and addresses.columns

Table companies

uuid	name	phone_number
5esd	A INC.	667-784-343
6dcv	B INC.	866-653-343
56js	C INC.	111-222-333
tug8	D INC.	111-222-333
jkj9	E INC.	777-666-443

Table Addresses

id	parent_uuid	a1	a2	postal
1	5esd	st2	st3	444
2	6dcv	st2	st3	444
3	56js	st55	st56	545
4	tug8	st77	st78	675

I need four rows:

uuid	name	phone_number
5esd	A INC.	667-784-343
6dcv	B INC.	866-653-343
56js	C INC.	111-222-333
tug8	D INC.	111-222-333

Because two first records has same addresses and two last records has same phone numbers

CodePudding user response：

One way:

select c.*
from companies c
where uuid in ( select parent_uuid 
                from (  select parent_uuid,
                               count(*) over(partition by a1,a2,postal) as cnt
                        from Addresses 
                       union all 
                         select uuid,
                                count(*) over(partition by phone_number) as cnt2 
                         from companies 
                      ) as tbl
                where cnt >1
              ) ;

https://dbfiddle.uk/EqGajA1t

With union all we find duplicates of each table getting the uuid and parent_uuid with duplicates and using the in operator containing the duplicated uuid and parent_uuid;

CodePudding user response：

Something like this will give the desired output :

SELECT uuid,name,phone_number
  FROM companies
  INNER JOIN Addresses
  ON companies.uuid = Addresses.parent_uuid;