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 |
|
lawrencef14
Starting Member
1 Post |
 Posted - 2006-10-26 : 15:13:22
|
| I am kind of new to this and trying to write a SQL statement that shows companies linked to a Parent Company. I can produce a list of companies where the ParentID is not blank and get the companies that have a parent. But what I really want is a list to show the Parent Company and all the Companies that have that Parent in the list below it? Is that possible? Also, some of the subs could have their own subs so that adds a layer of complexity.Thanks,Larry |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-10-27 : 08:58:12
|
| Hierarchies is one of the most complicated and misunderstood topics. I recommend you do some research on different graph models and their advantages and disadvantages. What you have discribed is called the Adjacency Model and is pretty much only applicable to Generalized Directed Acyclic Non-Reconvergant Graphs (aka Tree). Adjacency is how everyone (especial those without much exposure) seems to model hierarchies, but if you data deviates from that graph type you will run into problems quickly.The best way to model your tree is highly dependant on how it will be used. For example, the adjacency model is good when there is frequent data modification, but sucks when you are trying to do they query you are asking about. Things get even more complicated when you don't know how many levels there are to your tree (your case). The Nested Set model is fantistic for the query you are trying to write (and many others) regardless of depth and branches, but can perform poorly with frequent data modification. Another option for trees is the Materialized Path model, which is a good compromise, but can fall down in other ares.I recommend that you not only take the time to review Nigel's article (and also look into CTEs if you are using SQL2K5), but also do some reading about hierarchies modeling in relational systems.Jay White |
 |
|
|
|
|
|
SQL Statement Help