Duplicate Records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Duplicate Records

Author

Topic

Sim
Starting Member

12 Posts

Posted - 2006-04-06 : 13:22:28

Table1 has shop# and shop_id. Every shop# should have only one shop_ID. There has been a few data entry errors where a shop# has duplicate a shop_id. How to write a query for shop#s that have more than one shop_id?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2006-04-06 : 13:29:38

Sim, our FAQ has great information on dealing with duplicates.

---------------------------
EmeraldCityDomains.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-06 : 13:30:25

Check out this first query in the article:
http://www.sqlteam.com/item.asp?ItemID=3331

Add a unique constraint to fix this problem in the future.

Tara Kizer
aka tduggan

Sim
Starting Member

12 Posts

Posted - 2006-04-06 : 14:35:07

The problem is that I have duplicate records with the correct shop# and shop_id that I want to keep. I am only looking for those records where there are two distinct shop_ids associated with the shop#. Make sense?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-06 : 14:37:06

Please post a data example.

Tara Kizer
aka tduggan

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-06 : 15:38:23

"How to write a query for shop#s that have more than one shop_id?"
select shop# from myTable group by shop# having count(shop_id) > 1

"I am only looking for those records where there are two distinct shop_ids associated with the shop#"
select shop# from myTable group by shop# having count(distinct shop_id) = 2

A data example would be nice...

rockmoose

Sim
Starting Member

12 Posts

Posted - 2006-04-06 : 15:39:05

Data Example:
Shop# Shop_ID Column3 Column 4.......
5145 379
5145 379
5145 412

In this example, 379 is the correct shop_id. I want to run a query for all those shop#s that have more than one shop_ids associated with them.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-06 : 15:43:36

Did you look at the link?

What do you want the output of the query to be for the data example that you provided?

Tara Kizer
aka tduggan

Sim
Starting Member

12 Posts

Posted - 2006-04-06 : 15:48:41

Yes.

I have ~600 shop#s. I know this shop# (5145) has an erroneous shop_id associated with it. I want the query to have an output that includes shop #5145 and any other shop# that has more than one shop_id associated with it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-06 : 15:53:14

Try this:


SELECT t.ShopNo, y.ShopID
FROM YourTable y
INNER JOIN
(
	SELECT ShopNo, COUNT(*)
	FROM YourTable
	GROUP BY ShopNo
	HAVING COUNT(*) > 1
) t
ON y.ShopNo = t.ShopNo

Tara Kizer
aka tduggan

Sim
Starting Member

12 Posts

Posted - 2006-04-06 : 16:17:29

Result
Server: Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 't'.

Sim
Starting Member

12 Posts

Posted - 2006-04-06 : 16:21:23

Tara/Rockmoose,
This worked. select shop# from myTable group by shop# having count(distinct shop_id) = 2
Thank you very much for your help.

Sim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-06 : 16:23:00

Oops:


SELECT t.ShopNo, y.ShopID
FROM YourTable y
INNER JOIN
(
	SELECT ShopNo, COUNT(*) AS Dup
	FROM YourTable
	GROUP BY ShopNo
	HAVING COUNT(*) > 1
) t
ON y.ShopNo = t.ShopNo

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources