Help with Cascading SELECT in asp.net application

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

You did not mention which version of SQL server you are using. SQL Serve 2005
has some new recursive features that will enable you to have a single query
that will return all of the relevant records no matter how many tiers you
will have. In SQL Server 2000 you need to explicitly join the the Tiers table
depending on the number of tier you want to retrieve, and find all of its
tasks. For Example, if you want retrieve the first Tear, you will need to
have the following SQL:
SELECT Tasks.* FROM Tasks INNER JOIN Tears AS T5 ON T5.ID = Tasks.Tear_ID
INNER JOIN Tears AS T4 ON T4.ID = T5.Parent_ref INNER JOIN Tears AS T3 ON
T3.ID = T4.Parent_ref INNER JOIN Tears AS T2 ON T2.ID = T3.Parent_ref INNER
JOIN Tears AS T1 ON T1.ID = T2.Parent_ref WHERE T1.ID = @RequiredTear

This query will return all tasks ascosiated with all of the 5th level tears
ascosiated with the first level tear pressed. If you then want to retrieve
more tasks from other levels, you only need to use union to join the two
select statements that relate to the different levels.

I think you should try and build a function on the server side that will
build the full SQL Statement depending on the retrieved level on the fly.
--
GuyBar


"Stuart" wrote:

> 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 - a very useful pointer I gained from another forum to resolve this...

http://milambda.blogspot.com/2005/0...or-monkeys.html

"guybar" wrote:

> You did not mention which version of SQL server you are using. SQL Serve 2005
> has some new recursive features that will enable you to have a single query
> that will return all of the relevant records no matter how many tiers you
> will have. In SQL Server 2000 you need to explicitly join the the Tiers table
> depending on the number of tier you want to retrieve, and find all of its
> tasks. For Example, if you want retrieve the first Tear, you will need to
> have the following SQL:
> SELECT Tasks.* FROM Tasks INNER JOIN Tears AS T5 ON T5.ID = Tasks.Tear_ID
> INNER JOIN Tears AS T4 ON T4.ID = T5.Parent_ref INNER JOIN Tears AS T3 ON
> T3.ID = T4.Parent_ref INNER JOIN Tears AS T2 ON T2.ID = T3.Parent_ref INNER
> JOIN Tears AS T1 ON T1.ID = T2.Parent_ref WHERE T1.ID = @RequiredTear
>
> This query will return all tasks ascosiated with all of the 5th level tears
> ascosiated with the first level tear pressed. If you then want to retrieve
> more tasks from other levels, you only need to use union to join the two
> select statements that relate to the different levels.
>
> I think you should try and build a function on the server side that will
> build the full SQL Statement depending on the retrieved level on the fly.
> --
> GuyBar
>
>
> "Stuart" wrote:
>
> > 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
> >