please help construct an SQL query - Mailing list pgsql-sql
| From | jfc100@btopenworld.com (Joe) |
|---|---|
| Subject | please help construct an SQL query |
| Date | |
| Msg-id | 88d9f4b3.0305110727.243250b9@posting.google.com Whole thread Raw |
| List | pgsql-sql |
> 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)