CODEWITHSUNDEEP
sqlsql-servert-sql
hieko
hieko

Reputation: 393

remove duplicate relations in self join

I need to self join one table on the criteria that id should not be same but market should be same in this way i found relating market for diffrent ids but in doing so i got dupl;icate relations like 1-3 and 3-1 b ut i do not need both entries i only need first one 1-3 there are million of rows in table so please help me optimizing this query too.

 declare @tbl table (id int   , market varchar(100))

 insert into @tbl (id,market)
 values(1,'abc'),(2,'xyz'),(3,'abc')

 select a.id , a.market , b.id ,b.market 
 from  @tbl a join  @tbl b 
 on a.id <> b.id 
 and a.market = b.market

Upvotes: 2

Views: 2864

Answers (3)

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS: It will consider id as alphanumeric. First use subquery to return the set of duplicate market with not equal IDs then join as you have done in your example

SELECT t.id, t.market, t1.id, t1.market
FROM(
    SELECT market, MIN(id) id
    FROM @tbl
    GROUP BY market HAVING COUNT(DISTINCT id) > 1) t
INNER JOIN @tbl t1 ON t1.market = t.market
    AND t1.id <> t.id

Upvotes: 1

Pankaj Gadge
Pankaj Gadge

Reputation: 2814

Clean and simple

select abc.id, abc.market , xyz.id , xyz.market
from  @tbl abc join  @tbl xyz
on abc.id <> xyz.id and abc.market = xyz.market and abc.id < xyz.id

Working SQL Fiddle

Upvotes: 3

sanatsathyan
sanatsathyan

Reputation: 1763

Try this code : 

select a.id , b.id , a.market
from  @tbl a join  @tbl b 
on a.id <> b.id and a.market = b.market and a.id < b.id

Hope this helps!

Upvotes: 1

Related Questions