user7724540 user7724540 -4 years ago 82
MySQL Question

Display only duplicates from a UNION ALL query in MySQL

I have a query that gathers usernames from 4 UNIONS. How do I display only the duplicate users from that result? (ideally only displaying each duplicate once).

Thanks!

Here is the query:

(SELECT user1 AS user FROM friendships WHERE user2 = 'johnsmith')
UNION ALL
(SELECT user2 AS user FROM friendships WHERE user1 = 'johnsmith')
UNION ALL
(SELECT user1 AS user FROM friendships WHERE user2 = 'johndoe')
UNION ALL
(SELECT user2 AS user FROM friendships WHERE user1 = 'johndoe')

Answer Source

You can use aggregation:

SELECT user
FROM ((SELECT user1 AS user 
       FROM friendships 
       WHERE user2 = 'johnsmith' 
      ) UNION ALL 
      (SELECT user2 AS user 
       FROM friendships 
       WHERE user1 = 'johnsmith' 
      ) UNION ALL 
      (SELECT user1 AS user 
       FROM friendships 
       WHERE user2 = 'johndoe')
      ) UNION ALL 
      (SELECT user2 AS user 
       FROM friendships 
       WHERE user1 = 'johndoe'
      ) 
     ) u
GROUP BY user
HAVING COUNT(*) > 1;

You can probably express this so it does only one pass over the data:

SELECT (CASE WHEN user1 = 'johnsmith' THEN user2
             WHEN user2 = 'johnsmith' THEN user1
             WHEN user1 = 'johndoe' THEN user2
             WHEN user2 = 'johndoe' THEN user1
        END) as user
FROM friendships f
WHERE 'johnsmith' in (user1, user2) or 'johndoe' in (user1, user2)
GROUP BY user
HAVING COUNT(*) > 1;

Note that this is not exactly the same. It would count a friendship between 'johnsmith' and 'johndoe' only once.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download