Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Find duplicate entries on two columns

Posted on 2011-02-23
7
Medium Priority
?
713 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

782 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