nested membership. how to check in spite of nesting level? 1

[ipupkin]

Hi, All

I need an advice to build a query to a single table consisting of two columns: "THING", "PARENT OF THING".

This table describes tree-like hierarchy. The query should return all THINGs whose PARENT (direct, grand-, great-grand-, and so on) equals to, say, "Group_A":

Sample tree:

ROOT
|
+--Group_A
| |
| +--Sub_Aa
| | |
| | +--Sub_Sub_Aa1
| | | |
| | | +--thing P
| | | +--thing Q
| | |
| | +--thing R
| |
| +--thing S
|
+--Group_B
| |
. . . . . . and so on

The table describing this hierarchy is:

THING PARENT
=====================
Group_A ROOT
Sub_Aa Group_A
Sub_Sub_Aa1 Sub_Aa
thing P Sub_Sub_Aa1
thing Q Sub_Sub_Aa1
thing R Sub_Aa
thing S Group_A
Group_B ROOT
. . . . . . and so on

The result of query should be all subgroups and things nested inside Group_A, in spite of how deep:

THING
============
Sub_Aa
Sub_Sub_Aa1
thing P
thing Q
thing R
thing S

This would be no problem with subqueries if nesting level were limited to 2 or 3. But some THINGs and subgroups are 4 levels deep.

Can such nested membership be checked in SQL?

Best regards

 

[MichaelRed]

done with self-join





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ipupkin]

Michael,

thanks for reply

have I to make self join 4 times if my max nesting level is 4?

 

[MichaelRed]

no, a single join should be sufficient. Between "parentId" and "ThingId". Although it is not clear (*at least to me) that your specific structure and data are actually sufficient to do it.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ipupkin]

well, data in this two-column table IS suficient because of the following:

if the PARENT of every THING is known, then it is possible to list childern, children's children and so on of any THING

this is a task of expanding tree. I choose any node, and want to list all items of the branch.

I think that human words "parent of parent" correspond to self JOIN using THING and PARENT fields. Could you please give me a hint how to make such query to "look" several levels deep?

"human words" example:

THING PARENT
====================================
parent of aaa_1 is aaa
parent of aaa is aa
parent of aa is A
parent of a_1 is A
paretnt of A is ROOT

It is possible to say that children, children's children and so on of A are:

THING
===============
a_1
aa
aaa
aaa_1

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmm,


I think you must be better at this than I, so I will let you show the soloution





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[gol4]

what ipupkin is asking for would require a recursive query which is not possible in jet sql
see this thread

thread701-320225

 

[Quehay]

This kind of thing gets ugly quickly--are the nesting levels infinite or is there a fixed depth allowed? If there's a fixed depth it would clean things up to put in an attribute field for nesting level. What you're doing here is creating various subclasses of a supertype.

To start clearing the weeds a little:

if you select where parent_id is null you've got your highest level (along with any orphans...). Outer join these results to the table on parent_id = id. If you select where id not in (select unique parent_id) you should have the lowest level. In between it's pretty messy.