Join style duplicates - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Join style duplicates

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-04-15 : 17:52:37

Hi,

If I run..

select p1.productID, p2.productID
from products p1, products p2
where p1.productID = p2.productID

I'll get, for example,

1, 3
3, 1

in the row set.

Its kind of a duplicate. How do I only get one of the pair and for every product?

Cheers.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-15 : 17:56:53

select p1.productID
from products p1, products p2
where p1.productID = p2.productID

Tara

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-04-15 : 18:17:51

Thanks for your reply.

Sorry, I made a real hash out of that.

Actually, its like this...

products
--------
productID int
code varchar(50)

Some of the products have the same code. (They're duplicate products)

I want to discover all the duplicates.

So...

select p1.productID, p2.productID
from products p1, products p2
where p1.productID <> p2.productID and p1.code= p2.code

gives...
978, 976
976, 978

I don't need to know that 978 = 976 *and* 976 = 978. Just one will do.

That's what I'm after.

Cheers.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-04-15 : 18:33:30

Yah! I got it.

select max(p1.productID), min(p2.productID)
from products p1, products p2
where p1.productID <> p2.productID and p1.code= p2.code
group by p1.code

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-15 : 18:49:35

You should probably write that:

select
max(p1.productID),
min(p2.productID)
from
products p1
inner join products p2 on p1.code = p2.code
where
p1.productID <> p2.productID
group by
p1.code

Just to be using the standard.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-04-15 : 18:52:38

Well actually this is just one of those occasions when didn't. But I usually do use the standard because its more explicit.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-04-15 : 19:25:59

In case anyone's interested, I think this works better because the first solution only shows 2 products with the same code where as there may be more duplicates.

SELECT DISTINCT  p1.code, p1.productID, p1.prodName
FROM products p1
INNER JOIN products p2 on  p1.code= p2.code
WHERE p1.productID <> p2.productID
ORDER BY p1.code

kroky
Starting Member

14 Posts

Posted - 2004-04-16 : 03:49:53

i tihnk this is his problem since he want to remove the duplication
"ab" and "ba"

SELECT p1.code, p1.productID, p1.prodName
FROM products p1
INNER JOIN products p2 on p1.code= p2.code
WHERE p1.productID > p2.productID
ORDER BY p1.code

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources