Solved

Find duplicate entries on two columns

Posted on 2011-02-23
7
708 Views
Last Modified: 2012-05-11
How would I find table entries where the same values exist in two columns. My columns are EmployeeID and TrainingID
0
Comment
Question by:AkAlan
  • 4
  • 2
7 Comments
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 34961920
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
 
LVL 6

Author Comment

by:AkAlan
ID: 34961933
No, I think I may have entries where the employee has the same trainingID assigned more than once
0
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 34961975
do you want to post some example data of what you are trying to weed out?
0
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
LVL 6

Author Comment

by:AkAlan
ID: 34962022
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
 
LVL 6

Author Comment

by:AkAlan
ID: 34962143
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34962430
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
 
LVL 6

Author Comment

by:AkAlan
ID: 34962779
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question