Re: count children in recursive table

I think you meant it returned nothing for ID = 5

Try this :

SELECT P1.ID, P1.NAME, COUNT(P2.ID) AS children FROM UNIT P1, UNIT P2
WHERE P1.ID = 1
AND P2.SUPERID(+) = P1.ID
GROUP BY P1.ID, P1.NAME Pomme-granite

On Wed, 13 Aug 2003 16:27:03 +0200, Markus Joschko <jocsch_at_phreaker.net> wrote:

>Hi newsgroup,
>I have a problem with optimizing the query mechanism in a JAVA app with the
>following table:
>
>Table UNIT:
>
>ID | SUPERID | NAME
>--------------------------------
>1 | null | test
>2 | 1 | test
>3 | 1 | test
>4 | 3 | test
>5 | null | test
>
>
>Now I want to load the row with the ID 1 and I want to know if this row has
>children (these one with superid = 1). So I can decide in my JAVA prog if I
>do another call to get the children.
>
>Is it possible to do this in one call?
>
>I first started with
>
>SELECT P1.ID, P1.NAME, COUNT(P2.ID) AS children
>FROM UNIT P1, UNIT P2
>WHERE P1.ID = 1
>AND P2.SUPERID = 1
>GROUP BY P1.ID, P1.NAME
>
>But this returns nothing for ID = 1 cause there is no superid.
>Is it somehow possible to have this statement always returning the values of
>the row?
>
>I know there are efficient other possibilites to deal with a tree. But this
>is a production ready application where I can't change the DB Layout.
>Even stored procedures aren't considered as good. But if there is no other
>possibilty...
>
>
>Thanks,
> Markus
Received on Wed Aug 13 2003 - 12:21:01 CDT