How to find members tied to more than one caregiver in SQL

user_11069614 0
Viewed 38

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

member_id caregiver_first_name caregiver_last_name
512020204 AARON JOHNSON
304030303 AARON JOHNSON
002293993 ABEL MARTINEZ
383893933 ALEX JONES

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?

sql-server sql
2 Answers

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

          

          

            

              

                
                
                
              

            

            
          

          

            

              


              

            

            

              
                
              
            

            

              

                
                

                  

                    user_83398210
                  

                  
                    
                  
                

              

            

          

          

            


          

        

        
        

          

            
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.

          

          

            

              

                
                
                
              

            

            
          

          

            

              


              

            

            

              
                
              
            

            

              

                
                

                  

                    user_125257290
                  

                  
                    
                  
                

              

            

          

          

            


          

        

        
      

      

        


  
Related