Recursive SQL !

A quite powerful way to handle hierarchical model data : recursive SQL !

WITH tmp_table AS
 SELECT column1, column2, ...
 FROM src_table
 WHERE src_table.hierarch_column_id is NULL
 UNION ALL
 SELECT column1, column2, ...
 FROM src_table
 INNER JOIN tmp_table
 ON src_table.hierarch_column_id = tmp_table.column_id
SELECT *
FROM tmp_table

You can also add meta-information like 1 as n in the first select using src_table and n + 1 in the second join which lets you filter the level.