<B>Really Difficult Hierachical Query</B>

[Noonoo]

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...

 

[Hasse]

Noonoo, I probably won't be of much help, but who knows...

You're lucky with only a limited number of levels because then I might provide you a first step...
(As I've not enough time to read it all over again & work it out in detail, I hope I didn't make any logical errors...)
(And, in the end, I hope I didn't reinvent the wheel, having some much better white paper about hierarchical data issues elsewhere °-[)

First, as you talk about hiërarchical data, I suppose you're not talking about one-to-many relationships but about typical parent-to-child relationships (like: gender tree, construction units,...). Here I wonder why you don't use two tables instead of one? I would rather use:
E.g. tblPersons = ps_ID (long) + ps_IDParent (long) + ps_Name (string)
where ps_IDParent is related to ps_ID using a 'self/recursive join'.

Remarks:
- to avoid null values in the ps_IDparent field (and apply referential integrity,...), include an 'origin' record(/entry) (like ps_ID = ps_IDparent = 0, ps_name = "-") which can be used as the parentID for all highest-level records(/entries) (say the stems of the trees in the hiërarchy);
- if you can't just merge the two tables into this structure because both 'entities' have a number of different properties, you can reorganise them into one 'super'table with ID, parentID & common properties on which you can perform the self join and which is used furtheron and 1-1-related 'sub'tables in which you store the 'specific' properties (which you can merge with the supertable again later using queries). (**)
- this structure allows 'downward' branching only. To allow 'upward' branching too (like entering both mother and father), which seems to be the case in your situation, you need to split the table into tblPersons = ps_ID + ps_Name and tblRelations = re_ID + re_parentID (+ optional type,...)+ re_IDperson (foreign key). I assume this will complicate the next steps furtheron: you might be bound to use ID's instead of the calculated fields in the query in the second step; also if you want to use tblPersons-fields in your queries & reports, include all up-and-down-relatives (like nephews,...) "from mother's side", and so on... (***)
- other form,... design issues I leave out of scope here.

Second, a query can list for each record(/entry) all 'parents' and 'children' on all 6 higher and lower levels.
E.g. qryTree (1 level up & down) = SELECT tblPersons.ps_ID, IIf(Not IsNull([tblpersons_1Up]![ps_ID]),"(" & [tblpersons_1Up]![ps_ID] & ") " & [tblpersons_1Up]![ps_Name]) AS OneUp, tblPersons.ps_Name, IIf(Not IsNull([tblpersons_1Down]![ps_ID]),"(" & [tblpersons_1Down]![ps_ID] & ") " & [tblpersons_1Down]![ps_Name]) AS OneDown
FROM tblPersons AS tblPersons_1Down RIGHT JOIN (tblPersons LEFT JOIN tblPersons AS tblPersons_1Up ON tblPersons.ps_IDparent = tblPersons_1Up.ps_ID) ON tblPersons_1Down.ps_IDparent = tblPersons.ps_ID
WHERE (((tblPersons.ps_ID)<>0))
ORDER BY tblPersons.ps_ID, IIf(Not IsNull([tblpersons_1Up]![ps_ID]),"(" & [tblpersons_1Up]![ps_ID] & ") " & [tblpersons_1Up]![ps_Name]), IIf(Not IsNull([tblpersons_1Down]![ps_ID]),"(" & [tblpersons_1Down]![ps_ID] & ") " & [tblpersons_1Down]![ps_Name]);
(*) SQL of query with four levels at the end of this post

This query
- shows as columns: ID current record, higher level record ID & name's , current record name, lower levels record Id & name's
- uses calculated fields instead of ID's describing the related entries in the higher & lower levels
- uses outer joins to offer a complete listing of all 'relations'
- results in at least one record for each current record and one extra record for each extra branch present in the 'lower' levels;
- uses a criterium to exclude the 'origin' entry from the list
- sorts on (1) ID current record (2) ID's of related records, from the highest to the lowest level
So... maybe it might be a starting point for your purpose.

I'm sorry I've no time to dig deeper, but you might need some more
- queries, e.g. to include any 'up-and-down'-relatives too (like the current record's brothers, sisters, nephews & cousins, uncles & ounts,...). To go up and down the chain requires another query combining, I guess, the previous one and the original table;
- reports, in which you can group, let a value show up in a group only once for the first record,... and
- functions, like, to concatenate names from the children records,...

Is this of any help?

(*) SELECT tblPersons.ps_ID, IIf(Not IsNull([tblpersons_4Up]![ps_ID]),"(" & [tblpersons_4Up]![ps_ID] & ") " & [tblpersons_4Up]![ps_Name]) AS FourUp, IIf(Not IsNull([tblpersons_3Up]![ps_ID]),"(" & [tblpersons_3Up]![ps_ID] & ") " & [tblpersons_3Up]![ps_Name]) AS ThreeUp, IIf(Not IsNull([tblpersons_2Up]![ps_ID]),"(" & [tblpersons_2Up]![ps_ID] & ") " & [tblpersons_2Up]![ps_Name]) AS TwoUp, IIf(Not IsNull([tblpersons_1Up]![ps_ID]),"(" & [tblpersons_1Up]![ps_ID] & ") " & [tblpersons_1Up]![ps_Name]) AS OneUp, tblPersons.ps_Name, IIf(Not IsNull([tblpersons_1Down]![ps_ID]),"(" & [tblpersons_1Down]![ps_ID] & ") " & [tblpersons_1Down]![ps_Name]) AS OneDown, IIf(Not IsNull([tblpersons_2Down]![ps_ID]),"(" & [tblpersons_2Down]![ps_ID] & ") " & [tblpersons_2Down]![ps_Name]) AS TwoDown, IIf(Not IsNull([tblpersons_3Down]![ps_ID]),"(" & [tblpersons_3Down]![ps_ID] & ") " & [tblpersons_3Down]![ps_Name]) AS ThreeDown, IIf(Not IsNull([tblpersons_4Down]![ps_ID]),"(" & [tblpersons_4Down]![ps_ID] & ") " & [tblpersons_4Down]![ps_Name]) AS FourDown
FROM ((((((tblPersons AS tblPersons_1Down RIGHT JOIN (tblPersons LEFT JOIN tblPersons AS tblPersons_1Up ON tblPersons.ps_IDparent = tblPersons_1Up.ps_ID) ON tblPersons_1Down.ps_IDparent = tblPersons.ps_ID) LEFT JOIN tblPersons AS tblPersons_2Down ON tblPersons_1Down.ps_ID = tblPersons_2Down.ps_IDparent) LEFT JOIN tblPersons AS tblPersons_2Up ON tblPersons_1Up.ps_IDparent = tblPersons_2Up.ps_ID) LEFT JOIN tblPersons AS tblPersons_3Up ON tblPersons_2Up.ps_IDparent = tblPersons_3Up.ps_ID) LEFT JOIN tblPersons AS tblPersons_4Up ON tblPersons_3Up.ps_IDparent = tblPersons_4Up.ps_ID) LEFT JOIN tblPersons AS tblPersons_3Down ON tblPersons_2Down.ps_ID = tblPersons_3Down.ps_IDparent) LEFT JOIN tblPersons AS tblPersons_4Down ON tblPersons_3Down.ps_ID = tblPersons_4Down.ps_IDparent
WHERE (((tblPersons.ps_ID)<>0))
ORDER BY tblPersons.ps_ID, IIf(Not IsNull([tblpersons_4Up]![ps_ID]),"(" & [tblpersons_4Up]![ps_ID] & ") " & [tblpersons_4Up]![ps_Name]), IIf(Not IsNull([tblpersons_3Up]![ps_ID]),"(" & [tblpersons_3Up]![ps_ID] & ") " & [tblpersons_3Up]![ps_Name]), IIf(Not IsNull([tblpersons_2Up]![ps_ID]),"(" & [tblpersons_2Up]![ps_ID] & ") " & [tblpersons_2Up]![ps_Name]), IIf(Not IsNull([tblpersons_1Up]![ps_ID]),"(" & [tblpersons_1Up]![ps_ID] & ") " & [tblpersons_1Up]![ps_Name]), IIf(Not IsNull([tblpersons_1Down]![ps_ID]),"(" & [tblpersons_1Down]![ps_ID] & ") " & [tblpersons_1Down]![ps_Name]), IIf(Not IsNull([tblpersons_2Down]![ps_ID]),"(" & [tblpersons_2Down]![ps_ID] & ") " & [tblpersons_2Down]![ps_Name]), IIf(Not IsNull([tblpersons_3Down]![ps_ID]),"(" & [tblpersons_3Down]![ps_ID] & ") " & [tblpersons_3Down]![ps_Name]), IIf(Not IsNull([tblpersons_4Down]![ps_ID]),"(" & [tblpersons_4Down]![ps_ID] & ") " & [tblpersons_4Down]![ps_Name]);

(**) Difficult to find an example... fwiw: suppose you have a library database in which you keep information about library chapters, articles, books, series/compendia. Those items may (but not have to) be part of a higher level item (e.g. articles can be available as individual copies or as articles included in books,...) and you want to be free to store information on these items no matter what their actual physical condition is (e.g. information of both articles in a book - which your library doesn't possess 'physically' as individual items - and of individual articles). Then a super table might be tblItems = it_ID + it_ParentID + it_Type (article/book/...) + ... Subtables might be tblArticles = ar_IDitem + ar_Title + ..., tblCompendia = ..., a table with fields which only apply for physically autonomously managed items (price, provider, library ID,...), etc.

(***) To get an idea where this might lead to, I added a primary key 'ID' to tblPersons (loosing the possibility to inforce the self join's ref. integrity between ps_ID & ps_IDparent in the relationship window, but that's no drama). The query described in step two now showed for the current record an extra record for each upwardly oriented branch in the higher levels (like: mother) *but* it duplicates all 'downward' records too. Moreover, this still doesn't include any 'up-and-down'-relatives in the listing...

 

[Hasse]

ps I guess it's sometimes more appropriate to build the query'SQL described here from code, like when you have/want to include an indefinite/variable number of levels...

 

[ESquared]

Holy COW that was a thesis. I hope you're getting paid for this, Hasse!


NooNoo, I suggest you read up on hierarchical structures:

How do I do XXX with a hierarchical structure?

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[Hasse]

Indeed this took me a few hours... and spare time, actually... so I hope that what I wrote is a bit clear as well
(is it?)
If it's of any use, Noonoo may always offer me a drink when I come around .

Actually, it was fun too. I remember some past (idea's for) projects in which I had to deal with such hierarchical data problems too, without finding appropriate answers (for Access - SQL Server is not my cup of tea actually) and I guess this can happen again, so... the thinking effort should at least not be completely waisted...

BTW, when clicking your link, I ended up in tek-tips main page (?). Searching the site on "hierarchical data" & "... structure", however, I ended up with some interesting threads. Good lecture for on the train which I'm heading at now. In case I can't return over here, this seems most interesting (thread183-551980): "The ZEN of Recursion"

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/recursion.asp

 

[ESquared]

When I click the link it works fine.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[Hasse]

Sorry
- ESquared... I didn't notice you have to be logged in first... Indeed now the link works fine here too;
- I didn't realise I was living in the SQLserver-neighbourhood over here... Too bad you can't delete & modify any posts here :-[. The stuff I wrote is mostly from the (more limited) MS Access perspective. Hope it contains at least some useful toughts though

 

[ESquared]

By the way, you might look up the Nz() function in Access which could simplify some of your expressions from

[tt]Iif(Not IsNull(Expression),Expression,'')[/tt]

to

[tt]Nz(Expression,'')[/tt]

but I suppose that's an Access thing and doesn't belong in this forum so don't pay any attention to it.


You said that SQL server is not your cup of tea. Why? Now that I have learned it I find it far superior to Access in almost all respects.

-------------------------------------
A sacrifice is harder when no one knows you've made it.