|
|
You're in the
sql server programming
group:Help with cascading SELECT
sql server programming:
Hello
I have the following scenario that I need to address with which I would appreciate some help if anyone has the time: (Simply) I have an asp.net application: I have 2 tables: Table 1 is called Categories and is used to hold hierarchical data (Tiers) - these Tiers can be altered by the users. There may be 10 Categories in Tier 1, Tier 2 will hold 1 or more sub-categories for those 10, Tier 3 will have sub-categories of Tier 2 etc. etc. through to Tier 5 Table 2 is called Jobs and holds information pertaining to a specific task that is categorised by Table 1, so this would have single entries that you should be able to back-track through to Tier 1 of Table 1 This structure is to simply organise data in a highly retrievable structure. So when a user clicks on a Category in Tier 1 I want to retrieve all Jobs that are under that category regardless of the sub-category structure underneath, a click on a category in Tier 2 will filter these, a click on a sub category in Tier 3 will make the list smaller etc. etc. (I'm sure you get the picture) When a category or a job is defined and entered by the user a unique numeric reference is automatically assigned to that record - it also records the unique reference of the category above in a field called "Parent_Ref". I should therefore be able to take the parent record from a Job (which is the end of the line) and track this back to a sub-category in Tier 3, 4, or 5, this category record will also have a parent which goes back to Tier 2 and this will have a recod that tracks back to Tier 1 When the user initially clicks on a Tier 1 Category I need to do a search for all Table 1 records that have the unique reference of that Tier 1 category in their "Parent_Ref" field..... for all returned records I need to then do a search through Table 2 for all Jobs that are associated directly with that category. This will give me 0 to x returns from Jobs. I then need to requery and find all records from Tier 3 in Table 1 that have one of the unique references from the Tier 2 records that were just returned in their "Parent_Ref" field then retrieve all Jobs that relate to these retrieved records and loop through all the Categories in Table 1 until no more returns are possible I hope this is clear !?! I'm sure this is a standard scenario, I have researched the Select options in transact SQL but can not see the light. I appreciate that this will probably need to be a stored procedure that will be triggered by my asp.net application where I simply pass the unique ref in to SQL and let it do the search, but I do not know how to cascade the queries in the above fashion Thanks
Look at this example:
http://milambda.blogspot.com/2005/07/climbing-trees-is-for-monkeys.html ML ---
[quoted text, click to view] "Uri Dimant" wrote:
> Stuart > Can you post DDL+ sample data + expected result? > Like > > CREATE TABLE jobs > ( > .... > ..... > ) > > > > > > "Stuart" <Stuart@discussions.microsoft.com> wrote in message > news:133CA7C6-2A90-46C9-94A3-07C51F277741@microsoft.com... > > Hello > > > > I have the following scenario that I need to address with which I would > > appreciate some help if anyone has the time: > > > > (Simply) I have an asp.net application: > > I have 2 tables: > > > > Table 1 is called Categories and is used to hold hierarchical data > > (Tiers) - > > these Tiers can be altered by the users. There may be 10 Categories in > > Tier > > 1, Tier 2 will hold 1 or more sub-categories for those 10, Tier 3 will > > have > > sub-categories of Tier 2 etc. etc. through to Tier 5 > > > > Table 2 is called Jobs and holds information pertaining to a specific task > > that is categorised by Table 1, so this would have single entries that you > > should be able to back-track through to Tier 1 of Table 1 > > > > This structure is to simply organise data in a highly retrievable > > structure. > > > > So when a user clicks on a Category in Tier 1 I want to retrieve all Jobs > > that are under that category regardless of the sub-category structure > > underneath, a click on a category in Tier 2 will filter these, a click on > > a > > sub category in Tier 3 will make the list smaller etc. etc. (I'm sure you > > get > > the picture) > > > > When a category or a job is defined and entered by the user a unique > > numeric > > reference is automatically assigned to that record - it also records the > > unique reference of the category above in a field called "Parent_Ref". I > > should therefore be able to take the parent record from a Job (which is > > the > > end of the line) and track this back to a sub-category in Tier 3, 4, or 5, > > this category record will also have a parent which goes back to Tier 2 and > > this will have a recod that tracks back to Tier 1 > > > > When the user initially clicks on a Tier 1 Category I need to do a search > > for all Table 1 records that have the unique reference of that Tier 1 > > category in their "Parent_Ref" field..... for all returned records I need > > to > > then do a search through Table 2 for all Jobs that are associated directly > > with that category. This will give me 0 to x returns from Jobs. I then > > need > > to requery and find all records from Tier 3 in Table 1 that have one of > > the > > unique references from the Tier 2 records that were just returned in their > > "Parent_Ref" field then retrieve all Jobs that relate to these retrieved > > records and loop through all the Categories in Table 1 until no more > > returns > > are possible > > > > I hope this is clear !?! I'm sure this is a standard scenario, I have > > researched the Select options in transact SQL but can not see the light. I > > appreciate that this will probably need to be a stored procedure that will > > be > > triggered by my asp.net application where I simply pass the unique ref in > > to > > SQL and let it do the search, but I do not know how to cascade the queries > > in > > the above fashion > > > > Thanks > > > > Stuart > >
Thanks very much - seems to be just the thing ! - but in your experience
would this function in an acceptable way with a max of 1000 entries in the table ??? [quoted text, click to view] "ML" wrote:
> Look at this example: > http://milambda.blogspot.com/2005/07/climbing-trees-is-for-monkeys.html > > > ML > > ---
Sorry Uri - I spent some time composing a complete response to your request
only to have this interface bomb out when I posted it ! I appreciate your time, but the answer from ML actually answers my question.... [quoted text, click to view] "Uri Dimant" wrote:
> Stuart > ????? > > > > > > "Stuart" <Stuart@discussions.microsoft.com> wrote in message > news:147912F5-39C6-4868-B368-ED57D26CC465@microsoft.com... > > > > > > "Uri Dimant" wrote: > > > >> Stuart > >> Can you post DDL+ sample data + expected result? > >> Like > >> > >> CREATE TABLE jobs > >> ( > >> .... > >> ..... > >> ) > >> > >> > >> > >> > >> > >> "Stuart" <Stuart@discussions.microsoft.com> wrote in message > >> news:133CA7C6-2A90-46C9-94A3-07C51F277741@microsoft.com... > >> > Hello > >> > > >> > I have the following scenario that I need to address with which I would > >> > appreciate some help if anyone has the time: > >> > > >> > (Simply) I have an asp.net application: > >> > I have 2 tables: > >> > > >> > Table 1 is called Categories and is used to hold hierarchical data > >> > (Tiers) - > >> > these Tiers can be altered by the users. There may be 10 Categories in > >> > Tier > >> > 1, Tier 2 will hold 1 or more sub-categories for those 10, Tier 3 will > >> > have > >> > sub-categories of Tier 2 etc. etc. through to Tier 5 > >> > > >> > Table 2 is called Jobs and holds information pertaining to a specific > >> > task > >> > that is categorised by Table 1, so this would have single entries that > >> > you > >> > should be able to back-track through to Tier 1 of Table 1 > >> > > >> > This structure is to simply organise data in a highly retrievable > >> > structure. > >> > > >> > So when a user clicks on a Category in Tier 1 I want to retrieve all > >> > Jobs > >> > that are under that category regardless of the sub-category structure > >> > underneath, a click on a category in Tier 2 will filter these, a click > >> > on > >> > a > >> > sub category in Tier 3 will make the list smaller etc. etc. (I'm sure > >> > you > >> > get > >> > the picture) > >> > > >> > When a category or a job is defined and entered by the user a unique > >> > numeric > >> > reference is automatically assigned to that record - it also records > >> > the > >> > unique reference of the category above in a field called "Parent_Ref". > >> > I > >> > should therefore be able to take the parent record from a Job (which is > >> > the > >> > end of the line) and track this back to a sub-category in Tier 3, 4, or > >> > 5, > >> > this category record will also have a parent which goes back to Tier 2 > >> > and > >> > this will have a recod that tracks back to Tier 1 > >> > > >> > When the user initially clicks on a Tier 1 Category I need to do a > >> > search > >> > for all Table 1 records that have the unique reference of that Tier 1 > >> > category in their "Parent_Ref" field..... for all returned records I > >> > need > >> > to > >> > then do a search through Table 2 for all Jobs that are associated > >> > directly > >> > with that category. This will give me 0 to x returns from Jobs. I then > >> > need > >> > to requery and find all records from Tier 3 in Table 1 that have one of > >> > the > >> > unique references from the Tier 2 records that were just returned in > >> > their > >> > "Parent_Ref" field then retrieve all Jobs that relate to these > >> > retrieved > >> > records and loop through all the Categories in Table 1 until no more > >> > returns > >> > are possible > >> > > >> > I hope this is clear !?! I'm sure this is a standard scenario, I have > >> > researched the Select options in transact SQL but can not see the > >> > light. I > >> > appreciate that this will probably need to be a stored procedure that > >> > will > >> > be > >> > triggered by my asp.net application where I simply pass the unique ref > >> > in > >> > to > >> > SQL and let it do the search, but I do not know how to cascade the > >> > queries > >> > in > >> > the above fashion > >> > > >> > Thanks > >> > > >> > Stuart > >> > >> > >> > >
The performance of the function depends on two facts:
1) the number of rows in the table; and 2) the average depth of hierarchies. The best way to know would be to test it on your system. It should peform adequately with several thousands of rows with an average depth of about ten levels. When I say "adequatly" that does not mean "perfectly". In SQL 2005 the function should be redesigned using a recursive common table expression (rCTE), which might improve performance, since it's built into the SQL Server engine and requires less CPU time to execute. ML ---
Stuart
Can you post DDL+ sample data + expected result? Like CREATE TABLE jobs ( .... ..... ) [quoted text, click to view] "Stuart" <Stuart@discussions.microsoft.com> wrote in message news:133CA7C6-2A90-46C9-94A3-07C51F277741@microsoft.com... > Hello > > I have the following scenario that I need to address with which I would > appreciate some help if anyone has the time: > > (Simply) I have an asp.net application: > I have 2 tables: > > Table 1 is called Categories and is used to hold hierarchical data > (Tiers) - > these Tiers can be altered by the users. There may be 10 Categories in > Tier > 1, Tier 2 will hold 1 or more sub-categories for those 10, Tier 3 will > have > sub-categories of Tier 2 etc. etc. through to Tier 5 > > Table 2 is called Jobs and holds information pertaining to a specific task > that is categorised by Table 1, so this would have single entries that you > should be able to back-track through to Tier 1 of Table 1 > > This structure is to simply organise data in a highly retrievable > structure. > > So when a user clicks on a Category in Tier 1 I want to retrieve all Jobs > that are under that category regardless of the sub-category structure > underneath, a click on a category in Tier 2 will filter these, a click on > a > sub category in Tier 3 will make the list smaller etc. etc. (I'm sure you > get > the picture) > > When a category or a job is defined and entered by the user a unique > numeric > reference is automatically assigned to that record - it also records the > unique reference of the category above in a field called "Parent_Ref". I > should therefore be able to take the parent record from a Job (which is > the > end of the line) and track this back to a sub-category in Tier 3, 4, or 5, > this category record will also have a parent which goes back to Tier 2 and > this will have a recod that tracks back to Tier 1 > > When the user initially clicks on a Tier 1 Category I need to do a search > for all Table 1 records that have the unique reference of that Tier 1 > category in their "Parent_Ref" field..... for all returned records I need > to > then do a search through Table 2 for all Jobs that are associated directly > with that category. This will give me 0 to x returns from Jobs. I then > need > to requery and find all records from Tier 3 in Table 1 that have one of > the > unique references from the Tier 2 records that were just returned in their > "Parent_Ref" field then retrieve all Jobs that relate to these retrieved > records and loop through all the Categories in Table 1 until no more > returns > are possible > > I hope this is clear !?! I'm sure this is a standard scenario, I have > researched the Select options in transact SQL but can not see the light. I > appreciate that this will probably need to be a stored procedure that will > be > triggered by my asp.net application where I simply pass the unique ref in > to > SQL and let it do the search, but I do not know how to cascade the queries > in > the above fashion > > Thanks > > Stuart
Stuart
????? [quoted text, click to view] "Stuart" <Stuart@discussions.microsoft.com> wrote in message news:147912F5-39C6-4868-B368-ED57D26CC465@microsoft.com... > > > "Uri Dimant" wrote: > >> Stuart >> Can you post DDL+ sample data + expected result? >> Like >> >> CREATE TABLE jobs >> ( >> .... >> ..... >> ) >> >> >> >> >> >> "Stuart" <Stuart@discussions.microsoft.com> wrote in message >> news:133CA7C6-2A90-46C9-94A3-07C51F277741@microsoft.com... >> > Hello >> > >> > I have the following scenario that I need to address with which I would >> > appreciate some help if anyone has the time: >> > >> > (Simply) I have an asp.net application: >> > I have 2 tables: >> > >> > Table 1 is called Categories and is used to hold hierarchical data >> > (Tiers) - >> > these Tiers can be altered by the users. There may be 10 Categories in >> > Tier >> > 1, Tier 2 will hold 1 or more sub-categories for those 10, Tier 3 will >> > have >> > sub-categories of Tier 2 etc. etc. through to Tier 5 >> > >> > Table 2 is called Jobs and holds information pertaining to a specific >> > task >> > that is categorised by Table 1, so this would have single entries that >> > you >> > should be able to back-track through to Tier 1 of Table 1 >> > >> > This structure is to simply organise data in a highly retrievable >> > structure. >> > >> > So when a user clicks on a Category in Tier 1 I want to retrieve all >> > Jobs >> > that are under that category regardless of the sub-category structure >> > underneath, a click on a category in Tier 2 will filter these, a click >> > on >> > a >> > sub category in Tier 3 will make the list smaller etc. etc. (I'm sure >> > you >> > get >> > the picture) >> > >> > When a category or a job is defined and entered by the user a unique >> > numeric >> > reference is automatically assigned to that record - it also records >> > the >> > unique reference of the category above in a field called "Parent_Ref". >> > I >> > should therefore be able to take the parent record from a Job (which is >> > the >> > end of the line) and track this back to a sub-category in Tier 3, 4, or >> > 5, >> > this category record will also have a parent which goes back to Tier 2 >> > and >> > this will have a recod that tracks back to Tier 1 >> > >> > When the user initially clicks on a Tier 1 Category I need to do a >> > search >> > for all Table 1 records that have the unique reference of that Tier 1 >> > category in their "Parent_Ref" field..... for all returned records I >> > need >> > to >> > then do a search through Table 2 for all Jobs that are associated >> > directly >> > with that category. This will give me 0 to x returns from Jobs. I then >> > need >> > to requery and find all records from Tier 3 in Table 1 that have one of >> > the >> > unique references from the Tier 2 records that were just returned in >> > their >> > "Parent_Ref" field then retrieve all Jobs that relate to these >> > retrieved >> > records and loop through all the Categories in Table 1 until no more >> > returns >> > are possible >> > >> > I hope this is clear !?! I'm sure this is a standard scenario, I have >> > researched the Select options in transact SQL but can not see the >> > light. I >> > appreciate that this will probably need to be a stored procedure that >> > will >> > be >> > triggered by my asp.net application where I simply pass the unique ref >> > in >> > to >> > SQL and let it do the search, but I do not know how to cascade the >> > queries >> > in >> > the above fashion >> > >> > Thanks >> > >> > Stuart >> >> >>
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |