I need to find members that have a caregiver that also has other members. The sample data is like:

The query should return: 512020204 304030303 because they are both tied to AARON JOHNSON

I've tried

SELECT b.member_id FROM (
 SELECT member_id,
        caregiver_first_name,
        caregiver_last_name,
        row_number() over (partition by caregiver_first_name,caregiver_last_name order by caregiver_first_name,caregiver_last_name) as rn
  FROM caregivers
) b
WHERE b.rn > 1

but this will only give me the second id 304030303 since this was the second row number

How can I solve this?

CodePudding user response：

You should test of what queries is better performance wise. This answer makes minimal changes to your query.

create table cg(
  id integer,
  first_name varchar(100),
  last_name  varchar(100)
);

insert into cg(id, first_name, last_name)
values (512020204, 'AARON', 'JOHNSON'),
       (304030303, 'AARON', 'JOHNSON'),
       (002293993, 'ABEL', 'MARTINEZ'),
       (383893933, 'ALEX', 'JONES');
GO

4 rows affected

select id from (
 select id, count(id) over (partition by first_name, last_name) as n
 from cg
) as t
where t.n > 1
;
GO

|        id |
| --------: |
| 512020204 |
| 304030303 |

db<>fiddle here

CodePudding user response：

SELECT 
    member_id,
    b.cg_total
FROM caregivers AS a
LEFT JOIN ( 
     SELECT 
          caregiver_first_name, 
          caregiver_last_name,
          count(*) AS cg_total
     FROM caregivers
     GROUP BY 
          caregiver_first_name,
          caregiver_last_name
 ) AS b ON a.caregiver_first_name=b.caregiver_first_name AND
      a.caregiver_last_name=b.caregiver_last_name
WHERE b.cg_total>1

Basically, you have to join the table to a sub version of itself that is aggregated to count the number of times each caregiver appears. Then simply filter on the count. Alternatively, you could use an inner join and filter using HAVING within the sub-query. That might run faster if performance is a big issue.