1

I've a table with some ID's of a tree structured like this:

term1_id term2_id
1507       ;2
107        ;3
1          ;5
5          ;6
5          ;7
812        ;8
14         ;9
2719       ;10
9          ;11
9          ;12

This query teoretically extracts the path i need putting everything in an array:

WITH RECURSIVE cte(path) AS ( --Recursively call on path (Works for every length)
    SELECT array[r.term1_id, r.term2_id] AS path
    FROM   temp_table r
    LEFT   JOIN temp_table r0 ON r0.term1_id = r.term2_id
    WHERE  r0.term2_id IS NULL
UNION ALL 
    SELECT r.term1_id || c.path
    FROM   cte c
    JOIN   temp_table r ON r.term2_id = c.path[1]

),
max_len AS (
    SELECT max(array_length(path, 1)) max_len
    FROM cte
    )

select array_to_string(fill_with_last_element(path, 15), ',' , '*') 
from cte

But the problem is that it grabs the correct path but even the "subpath"

E.G.

path: {118;119;79;10935;77;12210;107;3}
subpath: {12210;107;3;3;3;3;3;3}
subpath: {10935;77;12210;107;3;3;3;3}

I can have only on father but father can have multiple sons

How can i avoid the creation of subpath?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.