Join table with itself for heirarchial system?

From: Benjamin Smith <bens(at)effortlessis(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Join table with itself for heirarchial system?
Date: 2003-07-17 01:34:08
Message-ID: 200307161834.08104.bens@effortlessis.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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?

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Oleg Bartunov 2003-07-17 02:37:49 Re: relevance
Previous Message jtx 2003-07-17 00:52:17 relevance