CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Internet Engineer
Internet Engineer

Reputation: 2534

Remove duplicates in Table Join with Case Statement

I need to be able to select distinct "phone" regardless if the phone comes from work or home. All phones need to be unique.

This query produces duplicates when the same phone is found in work and home in different rows.

SELECT CASE WorkPhone
        WHEN '' THEN 
            HomePhone
        ELSE 
            WorkPhone
        END AS Phone
       ,MAX(LastName)
       ,MAX(FirstName)
FROM   TableA
WHERE  (statusid = Inactive)
        AND 
            (modifieddate > '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

Upvotes: 0

Views: 1482

Answers (1)

Lamak
Lamak

Reputation: 70648

You should group by:

CASE WorkPhone
        WHEN '' THEN 
            HomePhone
        ELSE 
            WorkPhone
        END

instead of grouping by WorkPhone, HomePhone

Upvotes: 2

Related Questions