count children in recursive table

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 - 09:27:03 CDT