Thread: please help construct an SQL query
> 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)
On Sunday 11 May 2003 16:27 in <88d9f4b3.0305110727.243250b9@posting.google.com>, Joe (jfc100@btopenworld.com) wrote: I will re-post my follow-up. On Saturday 10 May 2003 07:21 in <88d9f4b3.0305092221.7827c597@posting.google.com>, Joe (jfc100@btopenworld.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@spamtrap.ntlworld.com (David W Noon) Remove spam trap to reply via e-mail. ======================================================