SQL Problem - Duplicate Names, listing all fields

1
votes

I think I have a generally easy SQL problem I need help with. I have a table I need to use which has the following fields: pmsid, ssn, lname, fname, rc, and budcode. Some of these fields are obvious, the others aren't important to my question.

I need to list the records in this set that have duplicate first names and last names. So if Joe Blow appears twice, I want his name to be on this list. I also need to show the other fields in the record. Simple so far?

I created the following SQL statement, which correctly lists the first and last names of the records in question. If I include all fields, I get no records at all. Here is what works so far:

SELECT lname, fname FROM [Employee]
WHERE lname != ' 'AND fname != ' '
GROUP BY lname, fname
HAVING COUNT(*) > 1

Then I tried to do the following to get the other fields listed, but I get an error, obviously:

SELECT * FROM [Employee]
WHERE lname, fname in 

(SELECT lname, fname FROM [Employee]  
WHERE lname != ' 'AND fname != ' '  
GROUP BY lname, fname  
HAVING COUNT(lname) > 1 and COUNT(fname) > 1)

ORDER BY lname, fname

I know I can't use multiple fields in the WHERE clause, but I'm not sure what else to try. Any thoughts?

3
sqlsql-server-2005
Concatenate the WHERE where lname || fname in (select lname||fname from ...). Of course I'm not sure how you concatenate in SQL Server, in Postgres it's the double pipe, in SQL Server it might be the + or the &, you'll have to check. - vol7ron

3 Answers

3
votes

I don't think you're that far off in your original solution, I'd just try appending the names to have one field instead of two, like:

SELECT * FROM [Employee]
WHERE lname + ', ' + fname in 
(SELECT lname + ', ' + fname as fullname FROM [Employee]
WHERE lname != ' ' AND fname != ' '
GROUP BY lname + ', ' + fname
HAVING COUNT(*) > 1)

there are many other approaches which would also work, and a couple have been posted already.

2
votes

Do you have an identity field or some other unique identifier for each row? If so, you could do a self join:

SELECT e.*
FROM Employee as E
INNER JOIN Employee as E2 
ON e.fname = e2.fname and e.lname = e2.lname
WHERE e.id <> e2.id
1
votes

You can use SUM aggregate function to achieve this: Sample Query:

SELECT *
  FROM (
        SELECT  pmsid, 
                ssn, 
                lname, 
                fname, 
                rc, 
                budcode, 
                SUM(1) OVER(PARTITION BY lname, fname) tsum
          FROM [Employee] a
       ) b
 WHERE  tsum > 1

Edit: Removed Order by Clause