Content-Type: text/plain;
  charset="us-ascii"
From: Benjamin Smith <bens@effortlessis.com>
Reply-To: bens@effortlessis.com
Organization: Effortless Information Systems
To: pgsql-sql@postgresql.org
Subject: Join table with itself for heirarchial system? 
Date: Wed, 16 Jul 2003 18:34:08 -0700
User-Agent: KMail/1.4.3
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200307161834.08104.bens@effortlessis.com>

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?