title: Inner join in SQL returns duplicate rows
body: <p>I have following 3 tables:</p>
  
  <pre><code>       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
  </code></pre>
  
  <p>And I need the output as (matching PersonId and IsLead = 'Yes'):</p>
  
  <pre><code>PersonId  PersonName ProviderName
  --------- ---------- ------------
    P1       ABC         Provider1
    P2       PQR         NULL
  </code></pre>
  
  <p>I am using below inner join on the above 3 tables but i m getting duplicate rows in the result.</p>
  
  <pre><code>  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
  </code></pre>
  
  <p>I tried to use distinct but even that didnt help.</p>
  
  <p>Any help  would be much appreciated!</p>