Re: Database query with subselect

amit.kulkarni_at_zensar.com (Amit K) wrote in message news:<2bc535e8.0402101954.16fbeaeb_at_posting.google.com>...
> theodor_ramisch_at_hotmail.com (Theodor Ramisch) wrote in message news:<12a479ae.0402100650.de612ba_at_posting.google.com>...
> > 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 Received on Wed Feb 11 2004 - 22:54:57 CET