SQL Group By with two fields-CodePudding

I'm using Postgresql. So I have table A with 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

Now 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
)

sqlfiddle

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.