Return a specific column of the deepest ancestor for all child records-CodePudding

I am researching recursion in SQL Server. I have the following query and I can't seem to get it to do what I want.

;WITH cte_txn AS (
SELECT
    vth_id,
    vth_pol_id,
    vth_moved_from_vth_id
FROM Variant_Transaction_Header
UNION ALL
SELECT
    e.vth_id,
    e.vth_pol_id,
    e.vth_moved_from_vth_id
FROM Variant_Transaction_Header e
JOIN cte_txn ON cte_txn.vth_moved_from_vth_id = e.vth_id
)

SELECT * FROM cte_txn;

If I specify a vth_id in the anchor (In this case "WHERE vth_id = 72418"), I get the following:

vth_id  vth_pol_id  vth_moved_from_vth_id
72418   NULL        57019
57019   NULL        53518
53518   803         NULL

Which is great. But now I want to convert the data above into:

vth_id  vth_pol_id  vth_moved_from_vth_id
72418   803         57019
57019   803         53518
53518   803         NULL

In other words, I want the vth_pol_id of the DEEPEST ancestor, regardless of which vth_id I plug in. What's the best way to do this?

EDIT: I should specify that I would want to get a result set that contains all branches, with the vth_pol_id essentially denoting which branch each record is on.

CodePudding user response：

I was trying to go from descendant to ancestor. The other way around is preferable as I need a value from the parent record to go with the child records. Here's the updated expression:

;WITH cte_txn AS (
SELECT
    vth_id,
    vth_pol_id,
    vth_moved_from_vth_id
FROM Variant_Transaction_Header
/*start at the base of the branch instead of the end.*/
WHERE NOT vth_pol_id IS NULL 
UNION ALL
SELECT
    e.vth_id,
    /* use parent's vth_pol_id */
    cte_txn.vth_pol_id,
    e.vth_moved_from_vth_id
FROM Variant_Transaction_Header e
/* Move away from ancestor instead of toward. */
JOIN cte_txn ON cte_txn.vth_id = e.vth_moved_from_vth_id
WHERE cte_txn.vth_id <> e.vth_id
)

/* Join again with vth for all records */
SELECT
v.vth_id,
c.vth_pol_id,
v.vth_moved_from_vth_id
FROM Variant_Transaction_Header v
LEFT OUTER JOIN cte_txn c ON c.vth_id = v.vth_id

The outer join is to make sure that if the root node happens to have a null value in the vth_pol_id column, all records are still returned. The simpler:

SELECT * FROM cte_txn;

can be used instead if records of this kind are to be omitted.