> 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(at)email(dot)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(at)email(dot)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(at)email(dot)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(at)email(dot)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(at)email(dot)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)