SQL Query: My brain hurts

Hi,

I need to run a complex (for me!) query on one table and am getting
nowhere.

It's for a menu/submenu system. I have three tables involved: Content,
Categories, and Content-Category Links (CCLinks). The "CCLinks" table
groups Content and Categories by pairing their IDs and assigning a
numeric "Order" to dictate the menu hierarchy. So if a piece of Content
only has one CCLinks entry with the Order of 1, then that piece of
content goes under a top-level menu item. If it has two CCLinks entries
with Order = 1 and Order =2 then it would appear under the following:

- Menu (1)
-- Submenu (2)

And so on.

It's a cinch retrieving the top level of categories, but I'm having
trouble querying the correct retrieval of subcategories that are related
to the top level. I figure once I can do it right for second level, I
can create some kind of loop to retrieve further sub-levels (although
for this project, I really only need two levels, I may want to use this
elsewhere).

I've been able to get it sort of working by running three separate
recordset queries within the top-level recordset to filter out the
correct results from the CCLinks table (all CategoryIDs with Order = 2
that have contentID in common with top Level CategoryID Order = 1), but
that takes forever and there must be some kind of more efficient way to
get the results, no?

Can you run multiple queries within one recordset using values retrieved
within that recordset to filter it further? Any ideas on a better way to
go about this?

(Using SQL Server 2000 and ASP)

Thanks.

RE: SQL Query: My brain hurts

You haven't posted enough info to get the help you need.

Post a simplified schema for the three tables.

Do you want a single recordset output?

Post a sample of the desired output, ensure the sample contains all the
different cases (eg. menu with no sub items, menu with one, menu with
multiple, menu with 3rd level menu under it, etc.)

With that info someone might be kind enough to write a bit of T-SQL to
create the desired output from the schema.



"Byron" wrote:

> Hi,
>
> I need to run a complex (for me!) query on one table and am getting
> nowhere.
>
> It's for a menu/submenu system. I have three tables involved: Content,
> Categories, and Content-Category Links (CCLinks). The "CCLinks" table
> groups Content and Categories by pairing their IDs and assigning a
> numeric "Order" to dictate the menu hierarchy. So if a piece of Content
> only has one CCLinks entry with the Order of 1, then that piece of
> content goes under a top-level menu item. If it has two CCLinks entries
> with Order = 1 and Order =2 then it would appear under the following:
>
> - Menu (1)
> -- Submenu (2)
>
> And so on.
>
> It's a cinch retrieving the top level of categories, but I'm having
> trouble querying the correct retrieval of subcategories that are related
> to the top level. I figure once I can do it right for second level, I
> can create some kind of loop to retrieve further sub-levels (although
> for this project, I really only need two levels, I may want to use this
> elsewhere).
>
> I've been able to get it sort of working by running three separate
> recordset queries within the top-level recordset to filter out the
> correct results from the CCLinks table (all CategoryIDs with Order = 2
> that have contentID in common with top Level CategoryID Order = 1), but
> that takes forever and there must be some kind of more efficient way to
> get the results, no?
>
> Can you run multiple queries within one recordset using values retrieved
> within that recordset to filter it further? Any ideas on a better way to
> go about this?
>
> (Using SQL Server 2000 and ASP)
>
> Thanks.
>