Help needed with SELECT query

[PO]

Hi,

I need some help with constructing a SELECT query:

WONUMBER PARENT AMOUNT HASCHILDREN
===========================================
1234 NULL 10 Y
2345 1234 20 Y
3456 1234 15 N
4567 2345 5 N

As you can see, wonumber 1234 has 2 children (2345 and 3456). Wonumber 2345
is a child of wonumber 1234 but also has a child of it's own - 4567.

SELECT WONUMBER FROM TABLE WHERE WONUMBER = '1234' OR PARENT = '1234'
would return wonumber 1234, 2345 and 3456. But what if I want to find
wonumber 1234's children and children's children? One further problem is
that the table can contain up to 7 nested levels of parent/children
relations. I.e.
wonumber 1234 could have children, who have children, who have children...

What I need is a select statement that would return all wonumbers that are
related to the top level (where the PARENT field contains a null value)
wonumber, regardless the number of levels.

TIA
Pete

 

[Allen Browne]

This is not a simple task in JET SQL.

One of the issues is that there is not an easy way to prevent a record being
its own grandparent or great-grandparent. It that does occur, you have an
infinite recursion, and so any query you do write cannot run to completion.

Joe Celko has written some stuff on this. Links:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

 

[John Spencer]

PERHAPS something like the following SQL statement

SELECT L1.WONumber
, L2.WONumber
, L3.WONumber
, L4.WONumber
, L5.WONumber
, L6.WONumber
, L7.WONumber
FROM (((((YourTable as L1
LEFT JOIN YourTable as L2
ON L1.WONumber = L2.Parent)
LEFT JOIN YourTable as L3
ON L2.WONumber = L3.Parent)
LEFT JOIN YourTable as L4
ON L3.WONumber = L4.Parent)
LEFT JOIN YourTable as L5
ON L4.WONumber = L5.Parent)
LEFT JOIN YourTable as L6
ON L5.WONumber = L6.Parent)
LEFT JOIN YourTable as L7
ON L6.WONumber = L7.Parent
WHERE L1.Parent is Null



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County