CODEWITHSUNDEEP
sqlsql-serverdatabase
Cade
Cade

Reputation: 97

Pseudo to SQL? All Records where FirstName & LastName Is duplicated but City is different?

I need to find all records that the FIRSTNAME is a duplicate and the LASTNAME is a duplicate but the city is different for the records where the names are duplicate.

So my data looks like this:

FirstName    LastName    CustomerFileLocation          City
----------------------------------------------------------------------
   Joe        Smith         c:\file1\File1.txt         Dallas
   Joe        Jones         c:\File2\File1.txt         New York
   Joe        Smith         c:\File3\File1.txt         New Mexico City
   Harry      Smith         c:\File4\File1.txt         Boca Raton
   Joe        Smith         c:\File3\File1.txt         Dallas
   Michael    Smith         c:\File1\File1.txt         Dallas

I want the query to return 

   Joe        Smith         c:\file1\File1.txt         Dallas
   Joe        Smith         c:\File3\File1.txt         New Mexico City

I wrote the following to find the matching FirstName and LastName. But I am not sure how to say "and City doesn't match"

SELECT        
   dbo.TblFileCache.FirstName, dbo.TblFileCache.LastName, 
   dbo.TblFileCache.ClaimFilePath, dbo.TblFileCache.Skip
FROM
   dbo.TblFileCache 
INNER JOIN
   (SELECT        
       FirstName, LastName, COUNT(*) AS CountOf
    FROM 
       dbo.TblFileCache AS tblFileCache_1
    GROUP BY 
       FirstName, LastName 
    HAVING         
       (COUNT(*) > 1)) AS dt ON dbo.TblFileCache.FirstName = dt.FirstName 
                             AND dbo.TblFileCache.LastName = dt.LastName
WHERE        
    (dbo.TblFileCache.Skip = 0)
ORDER BY 
    dbo.TblFileCache.FirstName, dbo.TblFileCache.LastName

Upvotes: 3

Views: 96

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

To get all the rows in your original data where one set of user names has multiple cities, you can use window functions:

select t.*
from (select t.*,
             min(city) over (partition by FirstName, LastName) as mincity,
             max(city) over (partition by FirstName, LastName) as maxcity
      from dbo.TblFileCache t
     ) t
where mincity <> maxcity;

If you want one row per city, you can do an aggregation on top of this:

select FirstName, LastName, min(CustomerFileLocation) as CustomerFileLocation, city
from (select t.*,
             min(city) over (partition by FirstName, LastName) as mincity,
             max(city) over (partition by FirstName, LastName) as maxcity
      from dbo.TblFileCache t
     ) t
where mincity <> maxcity
group by FirstName, LastName, City;

Upvotes: 4

Related Questions