Dado un id, buscar toda la jerarquía de hijos asociados #sql #hierarchy #children #mysql

mysql-hierarchical-data-from-parent-to-children-1.md

select      
            p4.parties_id as parent4_id,
            p3.parties_id as parent3_id,
            p2.parties_id as parent2_id,
            p1.parties_id as parent_id,
            p1.id as party_id,
            p1.name,
            concat(
            if(p4.parties_id,p4.parties_id,''),',',
            if(p3.parties_id,p3.parties_id,''),',',
            if(p2.parties_id,p2.parties_id,''),',',
            if(p1.parties_id,p1.parties_id,''),',',
            if(p1.id,p1.id,'')) as permissions
from        security.parties p1
left join   security.parties p2 on p2.id = p1.parties_id 
left join   security.parties p3 on p3.id = p2.parties_id
left join   security.parties p4 on p4.id = p3.parties_id
where       
	8 in (p1.parties_id, 
		p2.parties_id, 
		p3.parties_id, 
		p4.parties_id
	)
order       by 1, 2, 3, 4, 5;