Find duplicate entries on two columns

How would I find table entries where the same values exist in two columns. My columns are EmployeeID and TrainingID
LVL 6
AkAlanAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do to find the duplicate data:
select EmployeeID , TrainingID
  from yourtable
 group by EmployeeID , TrainingID
having count(*) > 1

Open in new window


if you now want to see all the relevant data:

select t.*
  from yourtable t
 join (select EmployeeID , TrainingID
  from yourtable
 group by EmployeeID , TrainingID
having count(*) > 1
) sq
on sq.TrainingID= t.TrainingID
 and sq.EmployeeID = t.EmployeeID

Open in new window

0
 
Atdhe NuhiuCommented:
Presumably not the same on the same record

Select * from table t1
inner join table t2
on t1.EmployeeID = t2.TrainingID

Does that do it?
0
 
AkAlanAuthor Commented:
No, I think I may have entries where the employee has the same trainingID assigned more than once
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Atdhe NuhiuCommented:
do you want to post some example data of what you are trying to weed out?
0
 
AkAlanAuthor Commented:
I have a database for tracking training on employees. I was trying ot clean it up by performing some updates manually yesterday and now I'm getting errors in a sproc:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Warning: Null value is eliminated by an aggregate or other SET operation.

I'm sure I created some duplicate records like where the same employee is assigned the same training more than once so I need to scrub my table for duplicate entries.

So if EmployeeID 1 had TrainingID 1 assigned more than once I need to delete on or more of the duplicate entries.
0
 
AkAlanAuthor Commented:
OK, I have narrowed it down to my training history table. In my sproc I'm using a subquery to get the max date an employee took a particular training and there must be entries where the same trainingID had the same date more than once. That doesn't change what I'm trying to do here though. I need to find duplicate records with the same TrainingID and DateComplete. Thanks for any help. I want to fix this before having to restore from backup.

0
 
AkAlanAuthor Commented:
OK, That worked. Thanks! I do have a follow up question which I can ask in another post if you want.
How would I go about deleting all but one of the duplicates in a sproc?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.