join table after duplicate search query 2

[boopers]

I need help adding a join statement to a query I wrote to find duplicate values in one table. here is what I have thus far:
select employeenum, count(*)
from employees
group by employeenum
having count(*)>1

Now I need to add a second table which will give me the employee names associated with the duplicate id's found in this table. Does anyone know the proper syntax/format? Should I create a temporary table out of this first query then do a separate query to join them? Looking for ideas..

 

[swampBoogie]

select employeeName
from employees e inner join empNames en
  on e.employeenum  = en.employeenum
group by employeeName
having count(*)>1

Since you didn't supply any table/columnnames I took a wild guess.

 

[boopers]

this isn't working. Here is what I wrote:
select lastname, firstname
from badges inner join employees
on badges.id = employees.id
group by employees.employeenum
having count(*)>1

I get an error message that the lastname and firstname field are invalid in the select statement as they are not a part of the aggregate function or group by clause. The names are most likely not duplicated, but different so i don't want to bring them into the group by clause.

both the Badges table and the employees table have an ID field which act as their index. it's the only thing to join on.

The duplicates are found in the employees table, employeenum field. I need to tie the names that are associated with those duplicates.

 

[cmgaviao]

Try this, similar to a question I asked but no one could answer....but it works:

SELECT EN.EMPLOYEENAME

FROM

EMPLOYEENAMES EN

WHERE EN.EMPLOYEENUM IN
(select employeenum, count(*)
from employees
group by employeenum
having count(*)>1)

 

[swampBoogie]

select lastname, firstname
from badges inner join employees
on badges.id = employees.id
group by lastname, firstname
having count(*)>1

 

[cmgaviao]

My apologies...disregard the last post...this way actually works....you can also do it similar to my first post using the EXISTS syntax...but as I don't use that often, I can't give an example without going to the SQL server help file...but look it up for future reference....

Enough talk...here's the working version:

SELECT DISTINCT EN.EMPLOYEENAME

FROM EMPLOYEENAMES EN,

(SELECT EMPLOYEENUM, COUNT(*) AS FOO
FROM EMPLOYEES
GROUP BY EMPLOYEENUM
HAVING COUNT(*) > 1) DUPS

WHERE EN.EMPLOYEENUM = DUPS.EMPLOYEENUM

 

[boopers]

Neither of these methods work. Maybe I should back up a little and give more detail.
I have one table which contains just the employee number and a separate ID that can be used to link to the other table.

Employees table
ID
EmployeeNum

Badges table
ID
Lastname
firstname

The duplicates are in the EmployeeNum field. For those that have the duplicates, I need to find all names associated with them.
I don't have an employeenum field in the badges table to check for dups. checking for distinct names won't help either.
everytime I try to add the names to my dup program, it tells me it is not a part of the aggregate function. Same with the ID which is the only thing I have to match on for the two tables. Help!

 

[swampBoogie]

So your data may look like

Employees table

ID ! EmployeeNum
1 ! 47
2 ! 47
3 ! 11

Badges table
ID ! Lastname ! firstname
1 ! Crepert ! Magdur
3 ! Nysto ! Ceptur

and in this case you would like to get the result

Crepert ! Magdur

select lastname, firstname
 from badges
 where id in (select e1.id 
                from employees e1 inner join employees e2
               on e1.EmployeeNum = e2.EmployeeNum
               and e1.id <> e2.id )

If I'm mistaken, give some sample data.

 

[boopers]

Your close...here is an example
employees table
ID! EmployeeNum
1 ! 2
2 ! 2

badges table
ID! LastName! FirstName!
1 ! Smith ! John!
2 ! Jones ! Karen!

The results I want would show that I have two EmployeeNum's that are duplicate (2) and what names in the badges table are listed for that. They need to clean up the data and decide who should belong to that employee number.

example
employeenum.employees Lastname.badges first name.badges
2 Smith John
2 Jones Karen

thank you so much for your help with this

 

[tlbroadbent]

[tt]
Select b.*
From badges b
Join employees e
On b.id=e.id
Join
(select employeenum
from employees
group by employeenum
having count(*)>1) q
On e.employeenum = q.employeenum

Or a less efficient query, easier for some to understand...

Select b.*
From badges b
Join employees e
On b.id=e.id
Where e.employeenum IN
(select employeenum
from employees
group by employeenum
having count(*)>1)[/tt]

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[swampBoogie]

[/code]
select EmployeeNum, lastname, firstname
from badges inner join employees
on badges.id = employees.id
where badges.id in (select e1.id
from employees e1 inner join employees e2
on e1.EmployeeNum = e2.EmployeeNum
and e1.id <> e2.id )
[/code]

 

[boopers]

I am sorry, I am not trying to be a pain, but I still can't get the right result set.
I need the results to have both the employeenum that is duplicated, and any names associated with it in the badges table. everytime I try to add that value to the select statement, it doesn't like it. Any ideas?

 

[boopers]

swampboogie-
the employeenum is not housed in the badges table, it is in the employees table. the only thing they have in common is that ID field, which is meaningless, except for creating the join.
basically, the dups are in employees, but it contains no useful information. badges has the names, but not employeenum.
I shouldn't try this stuff on a friday, my brain hurts!

 

[tlbroadbent]

Select b.*, e.employeenum
From badges b
Join employees e
On b.id=e.id
Join
(select employeenum
from employees
group by employeenum
having count(*)>1) q
On e.employeenum = q.employeenum


If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[boopers]

You are GREAT!!!!!

Thank you sooooo much. You saved me from my headache. I couldn't even think how to add the extra fields anymore.

thanks again!