This question already has an answer here:
I have a table that looks like this:
Where EMP has a child SEG and it has a children SUP and so on... What I would like to do is find the full hierarchy EMP>SEG>SUP>DIR>REG>GOP in any of the children based in something like 'where' clause (something like: 'where inferior_codigo = SUP0001'), they should return me all the hierarchy of this particular item.
I did something like this but i can only find the parent or children, not both at the same time, with something like this:
SELECT s1.inferior_codigo AS lv1, s2.inferior_codigo AS lv2, s3.inferior_codigo AS lv3, s4.inferior_codigo AS lv4, s5.inferior_codigo AS lv5, s6.inferior_codigo AS lv6 FROM subs AS s1 LEFT JOIN subs AS s2 ON s2.superior_codigo = s1.inferior_codigo LEFT JOIN subs AS s3 ON s3.superior_codigo = s2.inferior_codigo LEFT JOIN subs AS s4 ON s4.superior_codigo = s3.inferior_codigo LEFT JOIN subs AS s5 ON s5.superior_codigo = s4.inferior_codigo LEFT JOIN subs AS s6 ON s6.superior_codigo = s5.inferior_codigo WHERE s1.superior_codigo = 'EMP0001' LIMIT 25;
Which will result in the following:
Can someone tell me what I should do to accomplish this? Thanks in advance :)