SQL & PL/SQL

In my database, I have a self-referencing table, the table itself is for projects, and it allows users to get a hierarchical view of the company.


Here is the SQL (modifier is the term we use for project code, BBCI is the top project)

SELECT
modifier, modifierDescription, level
FROM
modifier
WHERE
level <= 2
CONNECT BY PRIOR
modifier = parentModifier
START WITH modifier = 'BBCI'
ORDER BY level

That perticular query gets the first two levels in the structure. I use this information to produce a tree structure in a web app.

But users have requested it would be good if in the tree structure is showed an + or - depending on whether there were anymore children under each parent, or better still the number of children under it, for example

BBCI
+ BBCI_CHILD
+ BBCI_CHILD2
- BBCI_CHILD3

or

BBCI
+ BBCI_CHILD (3 projects underneath)
+ BBCI_CHILD2 (2 projects underneath)
- BBCI_CHILD3 (0 projects underneath)


I am really stumped on this issue, and I am sure there is a way to do this in the web app, so for example do a query for each child node to see how many child nodes are underneath, but I figure it would be a lot tidier and faster if I could do it from a single SQL statement. Unfortunately I have tried to do this and am very much stuck.

If you need more information please let me know

Thanks!

Jon