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: enter image description 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


      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
    SELECT t.LinkedLogID
    FROM Log_LinkedLogs t INNER JOIN descendants as d ON t.LogID = d.LogID


FROM descendants

*Edit - Simplified table by changing node IDs to match picture.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.