Inner join in SQL returns duplicate rows

Question

I have following 3 tables:

       Person                        TeamMember                     Provider

 PersonId   PersonName   PersonId   ProviderId    IsLead     ProviderId   ProviderName
---------   ----------   --------  ------------  --------   ------------  --------
  P1         ABC            P1         PR1          Yes          PR1       Provider1
  P2         PQR            P1         PR2          No           PR2       Provider2
                            P1         PR3          No           PR3       Provider3
                          -------------------------------
                            P2         PR1          No
                            P2         PR2          No
                            P2         PR3          No

And I need the output as (matching PersonId and IsLead = 'Yes'):

PersonId  PersonName ProviderName
--------- ---------- ------------
  P1       ABC         Provider1
  P2       PQR         NULL

I am using below inner join on the above 3 tables but i m getting duplicate rows in the result.

  SELECT        P.PersonID, P.PersonName, 
         CASE WHEN TM.IsLead = 'Yes' THEN Pr.ProviderName
              WHEN TM.IsLead = 'No'  THEN NULL END AS ProviderName

  FROM            Person P INNER JOIN TeamMember TM ON P.PersonID = TM.PersonID 
                           INNER JOIN Provider PR ON Pr.ProviderId = TM.ProviderId

I tried to use distinct but even that didnt help.

Any help would be much appreciated!

Answer 1

For your solution to work properly you will need to use a LEFT JOIN and move the CASE expression to the JOIN clause. I believe the following query will fit your needs.

You can check an example on SQLFIDDLE.

SELECT
 P.PersonID
 ,P.PersonName
 ,Pr.ProviderName
FROM Person P
 LEFT JOIN TeamMember TM ON TM.PersonID = P.PersonID
                         AND TM.IsLead = 'Yes'
 LEFT JOIN Provider PR ON Pr.ProviderId = TM.ProviderId