From: jfc100@btopenworld.com
To: pgsql-sql@postgresql.org
Subject: please help construct an SQL query
Date: 11 May 2003 08:27:52 -0700
Message-ID: <88d9f4b3.0305110727.243250b9@posting.google.com> (raw)
> Hi,
>
> 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 |
> Primary key: pk_usergroupbean
>
> ... with each user having a fk reference to his/her group ...
>
> User table:test=# \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
>
> So, to summarize:
>
> Given user name, give me all the groups which this user belongs to in
> hierarchical order.
>
> Any help is much appreciated.
>
> joe
>
> example data:
> ==============
>
> test=# 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)
>
> test=# select * from userbean ;
> userid | username | password | firstname | surname | email
> | telmobile | telother | physicaladdress |
> buildingnumber | streetname | area | city |
> county | postcode | dob | usergroup
> --------+----------+----------+-----------+------------+-------------------+----------------+----------------+-----------------+----------------+------------------+-----------------+--------+----------------+----------+------------------------+-----------
> 1 | admin | password | Min | Strator | min@email.com
> | 07898 345 6785 | 0208 546 34555 | | 1
> | 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 |
> | 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 |
> | 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 |
> | 5 | Greenway Road | Nottinghill | Barnet | Greater
> London | W9 4VC | 1976-09-01 00:00:00+01 | 10
> (5 rows)
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: jfc100@btopenworld.com
Subject: Re: please help construct an SQL query
In-Reply-To: <88d9f4b3.0305110727.243250b9@posting.google.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