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