SQL help needed

[santosh1]

I wanted to get ID for employee where there was duplicate employees, because my goal is to find the duplicate
employee if it exists and delete it. I got an error message

' Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'

So, I did the next query using EXISTS, but didn't quite get the result I was expecting.

Is there someway to run the query# 1 differently because it is what I actually
want to do. Thanks. S.


Select ID
from dbo.Data
WHERE CITY in (select city, firstname, lastname from dbo.Data
group by city, firstname, lastname having count(*) > 1)
order by city

Select ID,FirstName,LastName,City
from dbo.Data
WHERE exists (select city, firstname, lastname from dbo.Data
group by city, firstname, lastname having count(*) > 1)
order by city

 

[GoDawgs]

Access has a find duplicates query wizard...try that out first.

 

[dodge20]

I did a query like this using oracle, I am not exactly sure if it will work in access, but you can give it a try.

select * from dbo.Data where (city, firstname, lastname) in (city, firstname, lastname
from dbo.Data
group by city, firstname, lastname
having count(*) > 1)
order by city, firstname, lastname;


Dodge20