X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id DCD9B476302 for ; Sun, 11 May 2003 11:27:56 -0400 (EDT) Received: from news.hub.org (news.hub.org [64.49.215.80]) by postgresql.org (Postfix) with ESMTP id 33244476262 for ; Sun, 11 May 2003 11:27:56 -0400 (EDT) Received: by news.hub.org (Postfix, from userid 8) id 1D09043A89E; Sun, 11 May 2003 11:27:56 -0400 (EDT) From: jfc100@btopenworld.com (Joe) X-Newsgroups: comp.databases.postgresql.sql Subject: please help construct an SQL query Date: 11 May 2003 08:27:52 -0700 Organization: http://groups.google.com/ Lines: 99 Message-ID: <88d9f4b3.0305110727.243250b9@posting.google.com> Content-Type: text/plain; charset=ISO-8859-1 X-Complaints-To: groups-abuse@google.com To: pgsql-sql@postgresql.org Content-Transfer-Encoding: quoted-printable X-Spam-Status: No, hits=-8.3 required=5.0 tests=BAYES_10,FROM_ENDS_IN_NUMS,QUOTED_EMAIL_TEXT autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/262 X-Sequence-Number: 13427 > Hi, >=20 > 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. >=20 > 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: >=20 > User groups table: > test=3D# \d usergroupbean > Table "usergroupbean" > Column | Type | Modifiers > -------------+---------+----------- > usergroupid | integer | not null > name | text | > parent | integer | > Primary key: pk_usergroupbean >=20 > ... with each user having a fk reference to his/her group ... >=20 > User table:test=3D# \d userbean > Table "userbean" > Column | Type | Modifiers > -----------------+--------------------------+----------- > userid | integer | not null > username | text | > password | text | > firstname | text | > surname | text | > email | text | > telmobile | text | > telother | text | > physicaladdress | text | > buildingnumber | text | > streetname | text | > area | text | > city | text | > county | text | > postcode | text | > dob | timestamp with time zone | > usergroup | integer | > Primary key: pk_userbean >=20 > So, to summarize: >=20 > Given user name, give me all the groups which this user belongs to in > hierarchical order. >=20 > Any help is much appreciated. >=20 > joe >=20 > example data: > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D >=20 > test=3D# select * from usergroupbean ; > usergroupid | name | parent > -------------+------------+-------- > 2 | anonymous | 1 > 3 | known | 1 > 5 | supplier | 3 > 6 | admin | 3 > 7 | customer | 3 > 8 | candc | 7 > 9 | agent | 5 > 10 | proprietor | 5 > 11 | retailer | 5 > 1 | user | > (10 rows) >=20 > test=3D# select * from userbean ; > userid | username | password | firstname | surname | email=20= =20 > | telmobile | telother | physicaladdress | > buildingnumber | streetname | area | city |=20=20=20= =20 > county | postcode | dob | usergroup > --------+----------+----------+-----------+------------+-----------------= --+----------------+----------------+-----------------+----------------+---= ---------------+-----------------+--------+----------------+----------+----= --------------------+----------- > 1 | admin | password | Min | Strator | min@email.com > | 07898 345 6785 | 0208 546 34555 | | 1=20=20=20=20= =20=20=20=20=20=20=20 > | Long Street | Ealing Common | London | Greater London | W7 > 4ER | 1968-01-01 00:00:00+00 | 6 > 2 | candc | password | Anthony | Denton | > anthony@email.com | 09878 345 6565 | 0207 345 5677 |=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20 > | 2 | Short Street | Bow | West Ham | > Greater London | W4 5YT | 1969-09-01 01:00:00+01 | 8 > 3 | prop | password | Charlie | Oakes | > charl@email.com | 07886 543 544 | 0208 435 6565 | | > 3 | Bling Avenue | Ealing | Ealing | Greater London > | SW3 4GT | 1948-06-07 01:00:00+01 | 11 > 4 | agent | password | Alex | Mockford | > alex@email.com | 09880876 76 | 0208 435 6643 |=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20 > | 4 | Ealing Lane | Ealing Broadway | Ealing | > Greater London | W5 6TE | 1971-01-09 00:00:00+01 | 9 > 5 | retailer | password | Paul | Ditchfield | > paul@email.com | 08897 345 552 | 0208 345 6677 |=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20 > | 5 | Greenway Road | Nottinghill | Barnet | Greater > London | W9 4VC | 1976-09-01 00:00:00+01 | 10 > (5 rows)