Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
alan.bates
Starting Member
1 Post |
 Posted - 2007-01-17 : 06:28:00
|
| Hi, I am new to SQL server and I am trying to teach myself as I go! - I'm trying to design a table structure to allow a simple hierarchy system to be build and display details of sites. I have create a table "structure" which has the following columns...structureIDcompanyId - Link to company tableroot - root - Is this the root node for a companyparentId - Id of this entries parent in the "structure" tabledescription - Description of this nodeenabled - Is this node activedetailId - Link to a "sitedetail" table which contains more details about this node - this is null if the node is not actually a site a just a parent node - to allow multiple site to later be linked together.So for example: 1 (root / company) | ------- | |(area 1) 2 3 (area 2) | | (site 1)4 ----- | | (site 2)5 6 (site 3)The numbers represnt the structure Id and the ()'s the descriptions. So within our company we have two area, area 1 has one site and area two has two sites.It is fairly trivial to be able to extract the root node:SELECT * FROM structure where CompanyID = @ID AND root = 'true'But what I want to be able to do is select all the children for a given node, so if my starting point was 2 it would return the details of row 3. If my starting point was 3 it would return the details of 5 & 6. If my starting point was 1 it would return the details of 2, 3, 4, 5 & 6!!! I also need to create a separate procedure for returning sibling's e.g if I am at position 5, the query would return 5 & 6!!!Sorry this is such a long first post, and asking for a lot of help, but if anyone has any ideas how to achieve this I would be most greatful :).Many Thanks,Alan |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-01-17 : 06:32:26
|
| This may help you:[url]http://www.nigelrivett.net/SQLTsql/RetrieveTreeHierarchy.html[/url]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
|
|
|
SQL Query Help!