Get source values for specific target value

Transact-SQL
1

Hi ,

How to get the previous values if we know the root value (587576) can someone please help me onto this.

tried my end i am no where near to the result :slight_smile:

Table and insert script

create table #tab(src int , trg int , name varchar(100))

insert into #tab values( 123456 , 987654 , 'xyz')
insert into #tab values( 678677 , 565636 , 'uvw')
insert into #tab values( 987654 , 879879 , 'abc')
insert into #tab values( 879879 , 587576 , 'ytc')
insert into #tab values( 654212 , 812432 , 'trc')

below is an image for an reference :

image

Expected result is :

col_name
879879
987654
123456

thanks in advance.

2

If possible, this type of processing is best done on an application server.

With tsql, one method is recursion:

WITH FindParents
AS
(
	SELECT src, 1 AS RLevel
	FROM #tab
	WHERE trg = 587576
	UNION ALL
	SELECT T.src, P.RLevel + 1
	FROM FindParents P
		JOIN #tab T
			ON P.src = T.trg
)
SELECT src
FROM FindParents
ORDER BY RLevel;