child to parent in same table – there must be a better way to do this

All we need is an easy explanation of the problem, so here it is.

i want to relate child to parent from same table.

Table is built like this.
id, parentid, name

i want to have the complete path of the [name] columns without specify levels like i have in the example. I would like to have a better way to display path with kind of infinite levels of parent and child. i hope this make sense

this is what i have

SELECT  case
            when is null and is null and is null and is null and is null  then
            when is null and is null and is null and is null                    then || ' > ' ||
            when is null and is null and is null                                      then || ' > ' || || ' > ' ||
            when is null and is null                                                        then || ' > ' || || ' > ' || || ' > ' ||
            when is null                                                                          then || ' > ' || || ' > ' || || ' > ' || || ' > ' ||
            else 'n/a' 
            end as path         
FROM    mytable s1
        LEFT JOIN mytable s2 ON s1.parentid =
        LEFT JOIN mytable s3 ON s2.parentid =
        LEFT JOIN mytable s4 ON s3.parentid =
        LEFT JOIN mytable s5 ON s4.parentid =
        LEFT JOIN mytable s6 ON s5.parentid =

thanks in advance

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

What you’re looking for is a recursive query:

with recursive nodes(id, path) as (
-- selecting the parent/root (anchor)
    from mytable r
    where = <choose starting root id>
union all
-- recursively going through child nodes (recursive member)
    select, concat(path, '->',
    from mytable c
        join nodes as n 
        on = c.parent_id
select *
from nodes
order by path;

Keep in mind that if you have self referencing members (or ones that reference each other in a circle), this will go into an infinite loop! So you have to either exclude such scenarios with a where statement or not allow any inserts/updates that would cause such a scenario.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply