postgresql7.1 with recursive site_node_tree as ( select node_id, parent_id, name, directory_p, pattern_p, object_id, 1 as tdepth from site_nodes where node_id = :node_id union all select c.node_id, c.parent_id, c.name, c.directory_p, c.pattern_p, c.object_id, tree.tdepth + 1 as tdepth from site_node_tree tree, site_nodes c where c.parent_id = tree.node_id ) select t.node_id, t.parent_id, t.name, t.directory_p, t.pattern_p, t.object_id, t.tdepth, p.package_key, p.package_id, p.instance_name, pt.package_type from site_node_tree t join apm_packages p on (t.object_id = p.package_id) join apm_package_types pt on (pt.package_key = p.package_key) order by tdepth, node_id select n.node_id, n.parent_id, n.name, n.directory_p, n.pattern_p, n.object_id, p.package_key, p.package_id, p.instance_name, t.package_type from site_nodes n left join apm_packages p on n.object_id = p.package_id left join apm_package_types t using (package_key) where n.node_id = :node_id