From: Benjamin Smith <bens@effortlessis.com>
To: pgsql-sql@postgresql.org
Subject: Join table with itself for heirarchial system?
Date: Wed, 16 Jul 2003 18:34:08 -0700
Message-ID: <200307161834.08104.bens@effortlessis.com> (raw)
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bens@effortlessis.com
Subject: Re: Join table with itself for heirarchial system?
In-Reply-To: <200307161834.08104.bens@effortlessis.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox