Find all parents for child

Database
Fan

Find all parents for child

WITH RECURSIVE RECTBL(child_ID, child_nm, Parent_ID, Parent_NM, LVL)AS
( SELECT CHILDid, childname, PARENTID, parentname, 0 AS lvl
FROM WDM_S_WCIS_GEN_S1.HIERARCHY
WHERE CHILDid = 1
AND SNAP_EFF_DT = CURRENT_DATE - 1

UNION ALL
SELECT CHILDid, childname, PARENTid,  parentname,
RECTBL.LVL + 1
FROM RECTBL
INNER JOIN HIERARCHY  ON PARENT_ID = CHILDid
AND child_id<>PARENT_ID
)
SELECT *
FROM RECTBL A
ORDER BY a.LVL;

Hi experts!

please help me figure out how to buld tabe I can pulld all parents  for one customer

I used code below to find all parents for one customer child.

ideally result table should  have 3 columns Customer ID , Parent ID and Level of ownership (1 - direct owner)

Customer  Parent Lvl

A1                B        1

A1                C        2

A1                D        3

A2                C         1

A2                 B        2

Thank you for your help!

Elena

Tags (1)