I need to query some hierarchical data. I've written a recursive query that allows me to examine a parent and all it's related children using an adjacency data model. The scenario is to allow users to track how columns are populated in an ETL process. I've set up the sample data so that there are two paths:

1. col1 -> col2 -> col3 -> col6

2. col4 - > col5

You can input a column name and get everything from that point downstream. The problem is, you need to be able to start at the bottom and work your way up. Basically, you should be able to put in col6 and see how the data got from col1 to col6. I'm not sure if it's a matter of rewriting the query or changing the schema to invert the relationships. Any input is welcome. Sample code below.

DECLARE @table_loads TABLE (column_id INT, parent_id INT, table_name VARCHAR(25), column_name VARCHAR(25))
DECLARE @column_name VARCHAR(10)

INSERT INTO @table_loads(column_id, parent_id, table_name, column_name)
SELECT 1,NULL,'table1','col1'
UNION ALL
SELECT 2,1,'table2','col2'
UNION ALL
SELECT 3,2,'table3','col3'
UNION ALL
SELECT 4,NULL,'table4','col4'
UNION ALL
SELECT 5,4,'table5','col5'
UNION ALL
SELECT 6,3,'table6','col6'

SELECT * FROM @table_loads


SET @column_name = 'col1'

;
WITH load_order(column_id, parent_id,table_name, column_name)
AS(
SELECT column_id, parent_id,table_name, column_name
FROM @table_loads
WHERE column_name = @column_name
UNION ALL
SELECT tl.column_id, tl.parent_id, tl.table_name, tl.column_name
FROM load_order lo
JOIN @table_loads tl
ON lo.column_id = tl.parent_id
)
SELECT * FROM load_order

Got it. It required a VERY subtle change in the join code:

WITH load_order(column_id, parent_id,table_name, column_name)
AS(
SELECT column_id, parent_id,table_name, column_name
FROM @table_loads
WHERE column_name = @column_name
UNION ALL
SELECT tl.column_id, tl.parent_id, tl.table_name, tl.column_name
FROM @table_loads tl
JOIN load_order lo
ON lo.parent_id = tl.column_id
)
SELECT * FROM load_order

• Edited by falcon00 10 hours 41 minutes ago
• Marked as answer by falcon00 10 hours 26 minutes ago

Try reversing the join condition inside your common table expression load_order. 

JOIN @table_loads tl
ON lo.parent_id = tl.column_id

Thanks

Bharath

• Marked as answer by falcon00 10 hours 25 minutes ago

Yeah that's what it took.