Nested Self Join - is it possible more than 2 levels?

Hi,

I am trying to take a Table that is hierarchical in nature and create a self join more than twice. I need three or four levels.

So my table is called Capability and has nested Capabilities going down. Structure is:

-Id

-Capability - Text

ParentCapId - Long Integer

Description - Text

I want to make a Join that ends up in a table that looks like:

Cap1, Descr1, Cap2, Descr2, Cap3, Descr3, Cap4, Descr4

I tried using the Join and it works for two levels but after two is a problem as the Join 1 does not work with the Join2.

Then I tried using a SQL statement but it keeps giving me an error that Entity "Capabilty" is undefined (or some similar error). I used Capability C1, Capability C2, etc.

Before I try too much longer, I'd like to know if it is possible to make a Self Join on the same table for more than 4 levels deep or not. Thanks in advance.

Hi Charles,

Sure it's possible, but is it what you really need ?

Your table definition allows for one to many relationships with a variable depth, a single parent might have many children, each record could have zero to many (grand)parents and zero to many (grand)children.  Such data is hard to represent in a single table with 4 levels.  For example if you just have one parent paired with one child, no other relatives, where do you put them in your resulting table ? as Cap3 and Cap4 or as Cap1 and Cap2?

What with parents having multiple children, do you add every possible chain of cap1, cap2, cap3, cap4 in your table?  And what part will you show if there are more than 4 links in a chain ?

What will you use this table for ?  For presenting the data to a user, you might be better off with some sort of hierarchical presentation like a tree.  


A lot to think about, but here's an example of how you could extract 4 levels into a table using an aggregate :


Option 1 : show all information in the database, some rows don't have 4 levels :

option 2 : only show those sets of data in the resulting table where all 4 levels are filled

with this being in the database :

this is result of both queries :

In the first option, you show a lot of repitition of the same information, in the second option, a lot of information is missing...

happy coding,

Dorine




Thanks Dorine for your quick reply :-)

I am trying to get With or without top option but with the the layout something like this (only showing Name) :

Level 1 ,          Level 2,             Level 3,              Level 4

---------------------------------------------

Food

Drinks

Drinks            Alcoholic Bev

Drinks            Sodas

Drinks           Sodas                 Cocoa

etc.

But I agree a Tree would be better, what would you advise using for that? I saw a JS tree plug-in I should check that out unless you know of a good one?

Ideally I'd like a visual diagrammatic representation of Nested Boxes...

Hello Charles, 

Remember reading a post where the hierarchy is flattened in such a way that you do not need to join to get the children. I think this was done by adding another extra column capturing the hierarchy  like for eg Parent1.Child1.Child11.Child111 etc. So if you needed the children at a certain level, you just need to filter on that column with say Parent1.Child1 to get all the children under it. 

Of course you would need to take into consideration the depth of the hiearchy. 


Hope this gives you some ideas. 


Regards

Amal

Solution

Hi Charles,

Have a look at my post about hierarchical data structure in relational databases in a topic titled Faster way to find records in an hierarchical data structure? 

In short there are several ways to efficiently store and query hierarchical data. Depending on your use case you should choose the correct pattern.

Regards,
Kilian

Solution

hi,

# you can use advance query widget for complex query