SQL Problem - Duplicate Names, listing all fields

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?

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.

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

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