Query Help: Data extraction from a single table in single query

1

Following is the table.

ACTION,ACTIONID,PREVIOUSACTIONID,DATA
Incoming,A0,,X
New,A1,,X1
Auto,A2,A1,X2
New,A3,,Y
Correct,A4,A2,X2
Execute,A5,A4,X3

I have initial ActionID as A5 so i need all the dependent data(based on PrevActionId) from the table..

So in this case..
All the rows containing.. ActionId as A5,A4,A2,A1

2

[code]declare @matchAction varchar(10) = 'A5';

declare @tbl table (
ACTION varchar(20),
ACTIONID varchar(10),
PREVIOUSACTIONID varchar(10),
DATA varchar(10)
)

insert into @tbl (ACTION,ACTIONID,PREVIOUSACTIONID,DATA)
values
('Incoming', 'A0', '', 'X'),
('New', 'A1', '', 'X1'),
('Auto', 'A2', 'A1', 'X2'),
('New', 'A3', '', 'Y'),
('Correct', 'A4', 'A2', 'X2'),
('Execute', 'A5', 'A4', 'X3');

-- select * from @tbl;

;with cte
as (
select ACTION,ACTIONID,PREVIOUSACTIONID,DATA
from @tbl t
where t.ACTIONID = @matchAction

union all

select t.ACTION, t.ACTIONID, t.PREVIOUSACTIONID, t.DATA
from @tbl t
inner join
cte c
on c.PREVIOUSACTIONID = t.ACTIONID
)
select *
from cte[/code]HTH

1 Like
3

Thanks a lot @stephen_hendricks :smile: