CODEWITHSUNDEEP
sqlsql-server
Emma
Emma

Reputation: 562

SQL Query based on common field

What SQL query do I need to use to get an alternative parts list based on a common reference number. The common reference in the Example below is ELS though the real table is over 100,000 rows with variable references.

Table A

**Reference     Part**
ELS           test1
ELS           test2  
ELS           test3

Result

**Part            Alternative Part**
test1           test2
test1           test3  
test2           test1
test2           test3
test3           test1  
test3           test2

Upvotes: 2

Views: 301

Answers (2)

GuidoG
GuidoG

Reputation: 12059

This can be done with a simple self join

create table #test (reference varchar(10), part varchar(10))
insert into #test values ('ELS', 'test1'), ('ELS', 'test2'), ('ELS', 'test3')

select t1.part as part, 
       t2.part as alternate_part
from   #test t1
  inner join #test t2 on t1.reference = t2.reference
where t1.part <> t2.part
order by 1

drop table #test

the result is this 

part    alternate_part  
----    --------------  
test1   test2   
test1   test3   
test2   test1   
test2   test3   
test3   test1   
test3   test2

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can use cross join:

select t1.part, t2.part
from t t1 join
     t t2
     on t1.els = t2.els and t1.part <> t2.part;

Upvotes: 2

Related Questions