Dado un id, buscar toda la jerarquía recursiva de padres asociados #sql #hierarchy #parent #recursive

mysql-hierarchical-data-from-parents-to-children.md

Visto en: https://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/

SELECT  CONCAT(REPEAT('    ', level  - 1), id) AS treeitem, parent, level
FROM    (
        SELECT  _id AS id, parent,
                @cl := @cl + 1 AS level
        FROM    (
                SELECT  @r AS _id,
                        (
                        SELECT  @r := parties.party_id
                        FROM    security.parties
                        WHERE   id = _id
                        ) AS parent,
                        @l := @l + 1 AS level
                FROM    (
                        SELECT  @r := 4, # id de la party que quieres buscar
                                @l := 0,
                                @cl := 0
                        ) vars,
                        security.parties h
                WHERE   @r <> 0
                ORDER BY
                        level DESC
                ) qi
        ) qo