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 |
|
JohnyStyles577
Starting Member
2 Posts |
 Posted - 2008-01-05 : 21:57:07
|
| I am a novice to intermediate level sql developer. I have a database design which I have a recursive relationship between tables which looks like this (simplified):TableGroup- Col1: TableGroup_ID (PK)TableOther:- TableOther_ID (PK)TableGroup_ParentChild- Col1: TableGroup_ParentChild_ID (PK)- Col2: TableGroup_Parent_ID (FK: TableGroup)- Col3: TableGroup_Child_ID (FK: TableGroup)TableGroup_Other- TableGroup_Other_ID (PK)- TableGroup_ID (FK: TableGroup)- TableOther_ID (FK:TableOther)so as you see, Groups can be related to each other using the ParentChild table, so that a given group can be the child of another. Additionally, Groups can be related to Other using the TableGroup_Other table.My question is, I want to be able to query these tables, to pull up all the above tables data associated with a given Group. So ideally, I would have a stored procedure that would give me 4 result sets, one for each of the above tables, with all of the related rows to a given Group. Because of the recursive relationship where you can relate a group to another group, and yet to another group, etc, these seems difficult without using cursors. I've come up with 2 possible solutions:1) Use cursors2) Use inner joins WITH Union Alls to get multiple levels of the relationship. Meaning run the first query to get the group requested. Run a 2nd query to get the childs of the group requested. Run a 3rd query to get the childs of the childs of the Group requested. Union All of these results together to get the result. of course with this option, there is a limit to how deep you can go (not really that big of a problem as the recursion is soft limited by 6 levels).Is there a better way to do this which I'm not thinking of? The 2nd approach yields ALOT of code. I'm leaning toward the first approach at the moment.As I'm not great with cursors, if the first approach is best, can someone give me an example of how they would do this?John Pequenohttp://www.iWebQuotes.comHealth Insurance Quotes you can Trust |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-06 : 00:26:09
|
| Are you using sql 2005. Then there's a better way of implementing recursion. SQL 2005 has CTEs which can be used for effective implementation of recursive queries. Refer to links below for more details and example usages:-[url]http://technet.microsoft.com/en-us/library/ms186243.aspx[/url][url]http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/[/url][url]http://blogs.conchango.com/christianwade/archive/2004/11/09/234.aspx[/url] |
 |
|
|
JohnyStyles577
Starting Member
2 Posts |
Posted - 2008-01-06 : 15:38:44
|
| Yes I am using SQL Server 2005, and that is perfect!!! I had a feeling there was a better way, CTE is perfect! Thanks goodness it's year >= 2005.John Pequenohttp://www.iWebQuotes.comHealth Insurance Quotes you can Trust |
|
|
|
|
|
|
|
|
Querying against a Recursive Table Design