Help in Tree Mapping query

Question

Hello- I want to search the Expcode of 'ST' for Maintree-'RD' only. I want to see all Expcode which is mapped related with 'ST'. If you see ExpCode-ST, it is mapped with 'DT'

if you see ExpCode-DT is mapped with 'DN'

if you see ExpCode-DN is mapped with 'DD'

Create table ERT (MainTree char(10), ExpCode char(4), MapCode char(4), Edate datetime)

Insert into ERT values ('RD','ST','DT','2020-01-01')

Insert into ERT values ('RD','DT','DN','2020-01-01')

Insert into ERT values ('RD','DN','DD','2020-01-01')

Insert into ERT values ('RD','DD', NULL,'2020-01-01')

So I want to see the following result from query -

Criteria - I want to see all Expcode which is mapped/related with Expcode-'ST'. Bascially I want to see all tree of Expcode-ST.

Maintree ExpCode MapCode Edate

RD DD NULL 2020-01-01

RD DT DN 2020-01-01

RD DN DD 2020-01-01

RD ST DT 2020-01-01

Answer 1

Hi @Kenny Gua

If I understand correctly, what you need is a recursive query.

;with CTE as(
  select * from ERT where ExpCode = 'ST'
  union all
  select A.* from ERT as A inner join CTE as B
  on A.ExpCode = B.MapCode)
select * from CTE;

Since there is something in your description that confuses me, I'm not sure if this is what you want, and if that's not what you want, please comment.

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Try this:

;with CTE as(
  select MainTree, ExpCode, MapCode , Edate,   ExpCode   as pExpCode ,0 as lvl
  from ERT where ExpCode = 'ST'
  union all
  select A.*,  b.pExpCode ,lvl+1 as lvl from ERT as A inner join CTE as B
  on A.ExpCode = B.MapCode)
select * from CTE
where pExpCode='ST'
order by lvl desc;