Women in Technology

Hear us Roar


Article:
  Hierarchical SQL
Subject:   Find ALL parents
Date:   2010-07-15 13:58:36
From:   clownbaby
This query is useful to find direct / indirect parents:


Given the following table structure:


id name reports_to_id reports_to_path
== ====== ============= ===============
1 'Bob' 0 '1'
2 'Dan' 1 '1.2'
3 'Joe' 2 '1.2.3'
4 'Mel' 2 '1.2.4'
5 'Stu' 4 '1.2.4.5'


The query:


select id, name from users where (select reports_to_path from users where id = 5) like '%'||id||'.%';


Would return:


id name
== =====
4 'Mel'
2 'Dan'
1 'Bob'

Recommended for You
 
Sign up today to receive special discounts,
product alerts, and news from O'Reilly.
Privacy Policy >
View Sample Newsletter >