0

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 s6.id is null and s5.id is null and s4.id is null and s3.id is null and s2.id is null  then s1.name
            when s6.id is null and s5.id is null and s4.id is null and s3.id is null                    then s2.name || ' > ' || s1.name
            when s6.id is null and s5.id is null and s4.id is null                                      then s3.name || ' > ' || s2.name || ' > ' || s1.name
            when s6.id is null and s5.id is null                                                        then s4.name || ' > ' || s3.name || ' > ' || s2.name || ' > ' || s1.name
            when s6.id is null                                                                          then s5.name || ' > ' || s4.name || ' > ' || s3.name || ' > ' || s2.name || ' > ' || s1.name
            else 'n/a' 
            end as path         
FROM    mytable s1
        LEFT JOIN mytable s2 ON s1.parentid = s2.id
        LEFT JOIN mytable s3 ON s2.parentid = s3.id
        LEFT JOIN mytable s4 ON s3.parentid = s4.id
        LEFT JOIN mytable s5 ON s4.parentid = s5.id
        LEFT JOIN mytable s6 ON s5.parentid = s6.id
;

thanks in advance

1 Answer 1

3

What you're looking for is a recursive query:

with recursive nodes(id, path) as (
-- selecting the parent/root (anchor)
    select r.id, r.name
    from mytable r
    where r.id = <choose starting root id>
   
union all
-- recursively going through child nodes (recursive member)
    select c.id, concat(path, '->', c.name)
    from mytable c
        join nodes as n 
        on n.id = 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.

2
  • works perfect many thanks. I had to change datatype on select "r.name" in first select statement to make join work. My bad since i did not provide datatype in the first place :)
    – wmjobem
    Apr 21 at 9:05
  • "in first select statement to make join work." of course i mean "in first select statement to make UNION to work."
    – wmjobem
    Apr 21 at 9:35

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.