Solved

Find duplicate entries on two columns

Posted on 2011-02-23
7
709 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Recursion schedule 13 48
Compare a column in results by values left of decimal 2 34
selective rebuild of SQL Tables in scheduled job 10 48
What is needed to become a DBA? 7 56
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 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