Get the full parent hierarchy from a given child in Postgresql

Question

Here is an example of the data I'm looking at:

Table:

id | name | parent_id _______|____________|______________ 1 |Root | null 2 |Parent #2 | 1 3 |Parent #3 | 1 4 |Parent #4 | 2 5 |Child #5 | 2 6 |Child #6 | 2 7 |Child #7 | 3 8 |Child #8 | 3 9 |Child #9 | 3 

Using a recursive query, I can start from the parent and get all the related children.

My question is: how can I start with a child and get all kindred parents, grandparents, etc., down to the root.

So, given Child # 9, I need a query that returns the following:

 id | name | parent_id _______|____________|______________ 1 |Root | 0 3 |Parent #3 | 1 9 |Child #9 | 3 

Any help would be greatly appreciated.

Answer 1

Like this:

 with recursive whosYourDaddy as ( --start with the "anchor" row select * from foo where id = 9 --parameterize me union all select foo.* from foo join whosYourDaddy on whosYourDaddy.parent_id = foo.id ) select * from whosYourDaddy order by id; 

Result:

 id name parent_id ----------------------- 1 Root (null) 3 Parent 3 1 9 Child 9 3 

Code:

http://sqlfiddle.com/#!15/a5fb9/12