I'm using Postgresql. I have table A with this sample data:
ID | NAME | HOME_PHONE | HAND_PHONE |
---|---|---|---|
1 | Peter | 0901111111 | 0811111111 |
2 | Peter | null | 0901111111 |
3 | Peter | 0811111111 | null |
4 | Marry | 0922222222 | 0822222222 |
5 | Marry | 0933333333 | 0922222222 |
6 | Jack | 0912345678 | null |
7 | Bob | 0944444444 | null |
8 | Bob | null | 0944444444 |
9 | Han | 0955555555 | null |
10 | Han | 0955555555 | null |
I need to get data with has same NAME and same HOME_PHONE, HAND_PHONE
Expected result: Peter, Marry, Bob, Han
- Peter has same Phone: 0901111111 or 0811111111 -> hit
- Marry has same Phone: 0922222222 -> hit
- Bob has same Phone: 0944444444 -> hit
- Han has same Phone: 0955555555 -> hit
How to query with using Group By
, Having
, etc....?
CodePudding user response:
You can try to use EXISTS
subquery with your logic, if you want to remove duplicate name that you can use DISTINCT
SELECT DISTINCT name
FROM A t1
WHERE EXISTS (
SELECT 1
FROM A tt
WHERE t1.NAME = tt.NAME
AND (
t1.HOME_PHONE = tt.HAND_PHONE
OR
t1.HAND_PHONE = tt.HOME_PHONE
OR
t1.HAND_PHONE = tt.HAND_PHONE
OR
t1.HOME_PHONE = tt.HOME_PHONE
) AND t1.id <> tt.id
)
If you want to get the name with a comma in one row you can try to use string_agg
function
SELECT string_agg(DISTINCT name,',')
FROM A t1
WHERE EXISTS (
SELECT 1
FROM A tt
WHERE t1.NAME = tt.NAME
AND (
t1.HOME_PHONE = tt.HAND_PHONE
OR
t1.HAND_PHONE = tt.HOME_PHONE
OR
t1.HAND_PHONE = tt.HAND_PHONE
OR
t1.HOME_PHONE = tt.HOME_PHONE
) AND t1.id <> tt.id
)
CodePudding user response:
You can try with a SELF JOIN
:
SELECT DISTINCT t1.name
FROM tab t1
INNER JOIN tab t2
ON t1.NAME = t2.NAME
AND (t1.HOME_PHONE = t2.HAND_PHONE
OR t1.HOME_PHONE = t2.HOME_PHONE
OR t1.HAND_PHONE = t2.HAND_PHONE)
Here's the SQL Fiddle: https://www.db-fiddle.com/f/s8RQa8Nptg4cmGEVjQHoUE/3.