SQL to query ancestors (unknown height but know max height)

10 May 2013

Let's say we store a tree in a MySQL table with the columns: id, name, and parent_id

We use left join to get all ancestors of it. For example, we know that the max height is 4, so we join 4 times. Here is the SQL statement:

SELECT l4.id AS l4_id, l4.name AS l4_name, l4.parent_id AS l4_parent_id, l3.id AS l3_id, l3.name AS l3_name, l3.parent_id AS l3_parent_id, l2.id AS l2_id, l2.name AS l2_name, l2.parent_id AS l2_parent_id, l1.id AS l1_id, l1.name AS l1_name, l1.parent_id AS l1_parent_id FROM `table` AS l4 LEFT JOIN `table` AS l3 ON l4.parent_id = l3.id LEFT JOIN `table` AS l2 ON l3.parent_id = l2.id LEFT JOIN `table` AS l1 ON l2.parent_id = l1.id WHERE l4.id IN (<some_node_we_want_ancestors>);