From: David W Noon <dwnoon@spamtrap.ntlworld.com>
To: pgsql-sql@postgresql.org
Subject: Re: please help construct an SQL query
Date: Sun, 11 May 2003 22:10:20 +0100
Message-ID: <sqq2p-bi9.ln1@my-pc.ntlworld.com> (raw)
References: <88d9f4b3.0305110727.243250b9@posting.google.com>
On Sunday 11 May 2003 16:27 in
<88d9f4b3.0305110727.243250b9@posting.google.com>, Joe
(jfc100@btopenworld.com) wrote:
I will re-post my follow-up.
On Saturday 10 May 2003 07:21 in
<88d9f4b3.0305092221.7827c597@posting.google.com>, Joe
(jfc100@btopenworld.com) wrote:
> Not being great with SQL, I was hoping to get some advice on how to
> construct an SQL statement that will give me a particular result.
>
> Basically I want to end up with a list of groups that a user belongs
> to. The list of group names should be in hierarchical order. The
> groups have a hierarchical relationship to each other as depicted in
> the following table structure:
>
> User groups table:
> test=# \d usergroupbean
> Table "usergroupbean"
> Column | Type | Modifiers
> -------------+---------+-----------
> usergroupid | integer | not null
> name | text |
> parent | integer |
I presume the column "parent" is, in fact, a self-referential key within
that table. In that case, the best approach is to build another table that
reflects the hierarchical structure, usually called a path enumeration
table. It is a little long-winded to go through here, but is well described
in a book titled "SQL for Smarties" [stop laughing, all you Australians!]
by a very knowledgeable author named Joe Celko.
A path enumeration table allows you to ensure the integrity of your
hierarchy, as well as sorting any cursor by each record's depth within the
hierarchy. It should solve all your problems in this case.
Indeed, I can recommend Celko's book to anybody faced with knotty problems
in database design and/or SQL coding. [I am not associated with Mr. Celko
in any way, other than as a satisfied reader.]
--
Regards,
Dave
======================================================
dwnoon@spamtrap.ntlworld.com (David W Noon)
Remove spam trap to reply via e-mail.
======================================================
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: dwnoon@spamtrap.ntlworld.com
Subject: Re: please help construct an SQL query
In-Reply-To: <sqq2p-bi9.ln1@my-pc.ntlworld.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