Question 21558 in Mysql Question posted in
The official MySQL documentation can be found here.

How to get all duplicates row in MySQL

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

Tags:

2

Answers


  1. 0

    Something like this will give the desired output :

    SELECT uuid,name,phone_number
  FROM companies
  INNER JOIN Addresses
  ON companies.uuid = Addresses.parent_uuid;
    Login or Signup to reply.
  2. 0

    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;

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search