Solved

Find duplicate entries on two columns

Posted on 2011-02-23
7
704 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query 8 50
Query to return total 6 19
SQL Server 2012 r2 - Calulations within stored procedure involving temp tables 6 30
How to search for strings inside db views 4 28
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard 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.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

810 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