I've tried to translate and implement the PostGres solution to the problem found here: PostgreSQL - retrieve all IDs in a tree for a given subnode, but I'm either programming it wrong, or misunderstanding the OP's table structure and how it differs from mine.
For any given node in a tree, I want to be able to return the entire tree and understand, at least, direct vs. indirect relationships of each node in that tree with that passed node. The passed node could have more than one ancestor and more than one descendant. I won't necessarily have access to the root node. The picture used in another SO post linked above illustrates what I'm dealing with pretty well, so I'm going to re-use it here:
Here's the data table I'm working with, where LogID is parent and LinekdLogID is child:
LogID | LinkedLogID |
---|---|
1 | 6 |
1 | 5 |
6 | 4 |
6 | 8 |
6 | 11 |
11 | 7 |
7 | 10 |
5 | 12 |
12 | 2 |
2 | 3 |
14 | 17 |
The problem I have is that when I pass a node that has no descendants, I don't receive anything. Passing node 10 for example (you could equate it to node #3 in the picture). The code does seem to work when I pass a node that is an ancestor to another node. Additionally, I need to be able to tell, for each node in the tree whether it's a direct parent or child of the passed node.
Below is the code I've translated using the above post:
DECLARE @passedLogId INT = '10'; --Problem node
WITH ancestors(parent) as
(
SELECT LogID[Parent]
FROM Log_LinkedLogs
WHERE LogID = @passedLogId
UNION ALL
SELECT t.LogID[Parent]
FROM Log_LinkedLogs t INNER JOIN ancestors a ON t.LinkedLogID = a.parent
),
descendants (LogID) AS
(
SELECT parent as LogID
from ancestors
UNION ALL
SELECT t.LinkedLogID
FROM Log_LinkedLogs t INNER JOIN descendants as d ON t.LogID = d.LogID
)
SELECT DISTINCT LogID
FROM descendants
*Edit - Simplified table by changing node IDs to match picture.