Pseudo in SQL? All records where FirstName & LastName are duplicated, but City is different?
I need to find all records that FIRSTNAME is a duplicate and LASTNAME is a duplicate, but the city is different for records where names are duplicated.
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 request 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 corresponding FirstName and LastName. But I'm not sure how to say "and the 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
+3
source to share
1 answer
To get all rows in the original data where one set of usernames 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 need one row per city, you can do an aggregation on top of that:
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;
+4
source to share