SQLTeam.com | Weblogs | Forums

Advanced Self Join Query Help


#1

Hi there!

I am looking for help to write a query on the table below :

Col A      Col B
 A             B
 B             C
 C             D
 E             F
 F             G

The output needs to be:

Col A      Col B
 A             D
 B             D
 C             D
 E             G
 F             G

What is the right way to approach this query ? Thanks for your help,
Vidya


#2

We will need a more information as to the logic that associates D to A, B, C.


#3

Imagine its like a "You may also know" linkedin relationship where A is connected to B, B to C and C to D. So we need to show that D is a potential connection for A.

Hope that helps.


#4

Hi

Please find solution

SELECT colA
	,CASE 
		WHEN colB <= 'D'
			THEN 'D'
		ELSE 'G'
		END
FROM #SampleData

/*
drop table #SampleData
create table #SampleData
(
ColA varchar(100) null,
ColB varchar(100) null
)

insert into #SampleData select 'A','B'
insert into #SampleData select 'B','C'
insert into #SampleData select 'C','D'
insert into #SampleData select 'E','F'
insert into #SampleData select 'F','G'

select * from #SampleData
*/