Can you query a set of nested entries to simulate a heirarchial system with a
single query?
I'm building a nested category table with a definition like below"
CREATE TABLE category (
id serial,
parent integer not null,
title varchar);
Idea is that we can "nest" categories so that we have
id parent title
----------------------------------------------
1 0 Clothing
2 1 Shirts
3 1 Pants
4 1 Socks
5 4 Male
6 4 Silk
So that, for example, id 6 would be
Clothing -> Socks -> Silk.
So far, I've only been able to derive this with 3 queries - 1 to get the
parent for id #6 (Silk) another to get the parent for id #4 (Socks) and
finally for id #1 (Clothing) and since parent ==0 I stop.
This seems wasteful - can this be done in a single query?
