This is a really difficult one...
I've got lot of data with hierachical relationships. The only way of ascertaining how many terms above or below an entry is running a query against a joined table.
eg. Select Table_Main.Main, Table_Higher.Higher from Table_Main Inner Join Table_Higher on Table_Main.Main Like Table_Higher.Main
This is fine and relatively straight forward if I want to find the terms immediately above or below. What I'll have to do, however, is obtain the entire hierachy for a particlar entry. This can split up to 6 levels up and down. eg. There might be 4 higher terms, each with 2 higher terms to them, each with differing amounts of higher terms in turn to them and so on, with the same for lower terms.
The complexity of the query soon becomes un-managable, though it seems possible there's some sort of looping process that might get help. I had attempted storing the first level in a table and using a looping cursor to generate a gradually increasing variable for output by could'nt make it happen.
To further complicate the situation I want to use the result of the query to generate XML or an alernative output which can be displayed via ASP as a hierachy on a webpage. I'm using SQL Server 7, so don't believe I can output XML.
If there's anyone out there who can tell me how to do this I'll be very grateful and pretty impressed to...
I've got lot of data with hierachical relationships. The only way of ascertaining how many terms above or below an entry is running a query against a joined table.
eg. Select Table_Main.Main, Table_Higher.Higher from Table_Main Inner Join Table_Higher on Table_Main.Main Like Table_Higher.Main
This is fine and relatively straight forward if I want to find the terms immediately above or below. What I'll have to do, however, is obtain the entire hierachy for a particlar entry. This can split up to 6 levels up and down. eg. There might be 4 higher terms, each with 2 higher terms to them, each with differing amounts of higher terms in turn to them and so on, with the same for lower terms.
The complexity of the query soon becomes un-managable, though it seems possible there's some sort of looping process that might get help. I had attempted storing the first level in a table and using a looping cursor to generate a gradually increasing variable for output by could'nt make it happen.
To further complicate the situation I want to use the result of the query to generate XML or an alernative output which can be displayed via ASP as a hierachy on a webpage. I'm using SQL Server 7, so don't believe I can output XML.
If there's anyone out there who can tell me how to do this I'll be very grateful and pretty impressed to...