sql query to find all linked rows-CodePudding

I have a table "users":

drop table users;
CREATE TABLE users(
id int,
user_id int,
phone_number VARCHAR(30),
email VARCHAR(30));

INSERT INTO users
VALUES
(1, 999, 61412308310, '[email protected] '),
(2, 129, 61477708777, '[email protected] '),
(3, 213, 61488908495, '[email protected]'),
(4, 145, 61477708777, '[email protected]'),
(5, 214, 61421445777, '[email protected]'),
(6, 214, 61421445326, '[email protected]');

I want to select all rows that have duplicate user_id or duplicate phone_number or duplicate email.

result should be:

2, 129, 61477708777, '[email protected] '
4, 145, 61477708777, '[email protected]'
5, 214, 61421445777, '[email protected]'
6, 214, 61421445326, '[email protected]'

id = 2 and id = 4 match the search terms (phone_number = 61477708777). id = 5 has the same email with row id 4, id=6 has the same user_id with id=5.

CodePudding user response：

Recursive query is what you need. It helps you express declaratively the reasoning of adding another rows for given seed row:

with recursive r (id, user_id, phone_number, email) as (
  select u.id, u.user_id, u.phone_number, u.email
  from users u
  where u.phone_number = 61477708777 -- or any initial condition
  union
  select u.id, u.user_id, u.phone_number, u.email
  from r
  join users u on (
    r.email = u.email
    or r.user_id = u.user_id
    --or add whatever condition
  )
)
select * from r

fiddle

CodePudding user response：

Problem with joining the table with itself multiple times using different conditions.

try:

SELECT a.*
FROM users a
JOIN (SELECT user_id, COUNT(*)
FROM users 
GROUP BY user_id
HAVING count(*) > 1 ) b
ON a.user_id = b.user_id
union
SELECT a.*
FROM users a
JOIN (SELECT email, COUNT(*)
FROM users 
GROUP BY email
HAVING count(*) > 1 ) b
ON a.email = b.email

This query first filters the rows based on the input user_id, then it checks for any matching email addresses. You can add union for the phone_number as well.

fiddle