I need to select certain fields for a multipart identifier that has duplicates.
I have the query to grab the duplicates correct.
SELECT b.MemEmpID, b.LastName, b.FirstName
FROM table1 As b
GROUP BY b.MemEmpID, b.LastName, b.FirstName
HAVING Count(*) > 1
But after finding the duplicates, I need to grab more information from the same table but only for the multipart identifier that has duplicates. So something like the following.
Can someone please help me with the correct syntax for this?
SELECT a.memempid, a.depkey, a.lastname, a.firstname, a.birthdate, a.memrelation
FROM table1 As a
INNER JOIN(SELECT b.MemEmpID, b.LastName, b.FirstName
FROM table1 As b
GROUP BY b.MemEmpID, b.LastName, b.FirstName
HAVING Count(*) > 1)
ON b.memempid = a.memempid
AND b.lastname = a.lastname
AND b.firstname = a.firstname
You were really close. You need to alias the subquery.
table1 As b
looses it's scope with the grouping ()
so you need to alias the parenthesized subquery. You could reuse b
- it will only know about the returned columns, but for clarity I chose (...) as c
to disambiguate.
SELECT a.memempid, a.depkey, a.lastname, a.firstname, a.birthdate, a.memrelation
FROM table1 As a
INNER JOIN
(SELECT b.MemEmpID, b.LastName, b.FirstName
FROM table1 As b
GROUP BY b.MemEmpID, b.LastName, b.FirstName
HAVING Count(*) > 1
) as c
ON c.memempid = a.memempid
AND c.lastname = a.lastname
AND c.firstname = a.firstname