Database query with subselect

[Theodor Ramisch]

Hi there,

I've got a simple table with an ID and a parentID field which build the relation
between the entries. Now I want to select some entries and count their childs.

This is what I tried:

SELECT NAME, ID, parentID,
(SELECT COUNT(ID) FROM myTable WHERE parentID=???ID???)
AS CHILDS FROM myTable WHERE parentID=0 ORDER BY parentID ASC

My problem is how to tell oracle to use the ID of the current entry in the
main query, not the ID field in the subselect.

Can anybody help me?

Thanks,
Theodor

[Amit K]

th*************@hotmail.com (Theodor Ramisch) wrote in message news:<12*************************@posting.google.c om>...

Hi there,

I've got a simple table with an ID and a parentID field which build the relation
between the entries. Now I want to select some entries and count their childs.

This is what I tried:

SELECT NAME, ID, parentID,
(SELECT COUNT(ID) FROM myTable WHERE parentID=???ID???)
AS CHILDS FROM myTable WHERE parentID=0 ORDER BY parentID ASC

My problem is how to tell oracle to use the ID of the current entry in the
main query, not the ID field in the subselect.

Can anybody help me?

Thanks, ================================================== ===
Use the aliases thr
like FOM myTable A etc
and then use A.ID in select

it will work Theodor

[Dave]

am***********@zensar.com (Amit K) wrote in message news:<2b**************************@posting.google. com>...

th*************@hotmail.com (Theodor Ramisch) wrote in message news:<12*************************@posting.google.c om>...

Hi there,

I've got a simple table with an ID and a parentID field which build the relation
between the entries. Now I want to select some entries and count their childs.

This is what I tried:

SELECT NAME, ID, parentID,
(SELECT COUNT(ID) FROM myTable WHERE parentID=???ID???)
AS CHILDS FROM myTable WHERE parentID=0 ORDER BY parentID ASC

My problem is how to tell oracle to use the ID of the current entry in the
main query, not the ID field in the subselect.

Can anybody help me?

Thanks,

================================================== ===
Use the aliases thr
like FOM myTable A etc
and then use A.ID in select

it will work

Theodor

Why not just do this...

Select n.name, count(p.id) as childs
from myTable p,
myTable n
where p.parent_id in ( ?, ?, ? ) <-- if you want to filter
and n.id = p.parent_id
group by n.name
order by n.name asc