title: Remove duplicates in Table Join with Case Statement
body: <p>I need to be able to select distinct "phone" regardless if the phone comes from work or home. All phones need to be unique.</p>
  
  <p>This query produces duplicates when the same phone is found in work and home in different rows.</p>
  
  <pre><code>SELECT CASE WorkPhone
          WHEN '' THEN 
              HomePhone
          ELSE 
              WorkPhone
          END AS Phone
         ,MAX(LastName)
         ,MAX(FirstName)
  FROM   TableA
  WHERE  (statusid = Inactive)
          AND 
              (modifieddate &gt; '11/11/2011')       
          AND 
              NOT EXISTS (SELECT Phone FROM TableB WHERE Phone = WorkPhone OR Phone = HomePhone)      
          AND 
              NOT EXISTS (
              SELECT AreaCode + PhoneNumber FROM TableC 
              WHERE   
                  (AreaCode = LEFT(WorkPhone,3) AND PhoneNumber = Substring(WorkPhone, 4, 7)
                  )
                  OR
                  (AreaCode = LEFT(HomePhone,3) AND PhoneNumber = Substring(HomePhone, 4, 7))                             
                  )
  
  GROUP BY WorkPhone, HomePhone
  </code></pre>