Re: please help construct an SQL query

On Sunday 11 May 2003 16:27 in
<88d9f4b3(dot)0305110727(dot)243250b9(at)posting(dot)google(dot)com>, Joe
(jfc100(at)btopenworld(dot)com) wrote:

I will re-post my follow-up.

On Saturday 10 May 2003 07:21 in
<88d9f4b3(dot)0305092221(dot)7827c597(at)posting(dot)google(dot)com>, Joe
(jfc100(at)btopenworld(dot)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(at)spamtrap(dot)ntlworld(dot)com (David W Noon)
Remove spam trap to reply via e-mail.
======================================================