Select with Inner Join with Multipart Identifier and Having Clause

user2136054 picture user2136054 · Mar 5, 2013 · Viewed 8.8k times · Source

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

Answer

Jay Walker picture Jay Walker · Mar 5, 2013

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