Help needed with SELECT query   

Hi,

Don't know if this is the right forum...
Anyways I need some help with constructing a SELECT query:


The table below is an example:

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 hierarchy levels of parent/children. 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 wonumber, regardless the number of levels.

TIA
Pete

date: Wed, 2 Jul 2008 11:45:54 +0200   author:   PO h

Re: Help needed with SELECT query   

I think you could perform a join on the table itself
like

SELECT
W1.WONUMBER AS PARENT,
W2.WONUMBER AS CHILD1,
W3.WONUMBER AS CHILD2,
W4.WONUMBER AS CHILD3,
W5.WONUMBER AS CHILD4,
W6.WONUMBER AS CHILD5,
W7.WONUMBER AS CHILD6
FROM
WO AS W6 LEFT OUTER JOIN
WO AS W7 ON W6.WONUMBER = W7.PARENT RIGHT OUTER JOIN
WO AS W5 ON W6.PARENT = W5.WONUMBER RIGHT OUTER JOIN
WO AS W4 ON W5.PARENT = W4.WONUMBER RIGHT OUTER JOIN
WO AS W3 ON W4.PARENT = W3.WONUMBER RIGHT OUTER JOIN
WO AS W2 ON W3.PARENT = W2.WONUMBER RIGHT OUTER JOIN
WO AS W1 ON W2.PARENT = W1.WONUMBER

That should result in an output like

PARENT      CHILD1      CHILD2      CHILD3      CHILD4
CHILD5      CHILD6
----------- ----------- ----------- ----------- -----------
----------- -----------
1234        2345        4567        NULL        NULL
NULL        NULL
1234        3456        NULL        NULL        NULL
NULL        NULL
2345        4567        NULL        NULL        NULL
NULL        NULL
3456        NULL        NULL        NULL        NULL
NULL        NULL
4567        NULL        NULL        NULL        NULL
NULL        NULL


On 2 Jul., 11:45, "PO" <h> wrote:

>
> 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 hierarchy levels of parent/children. 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 wonumber, regardless the number of levels.
>
> TIA
> Pete

date: Wed, 9 Jul 2008 06:23:43 -0700 (PDT)   author:   minimalniemand