I have a table that looks like this:

hierarchy table

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:

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:

Query Result

Can someone tell me what I should do to accomplish this? Thanks in advance :)

Browse other questions tagged or ask your own question.