Database query with subselect

From: Theodor Ramisch <theodor_ramisch_at_hotmail.com>
Date: 10 Feb 2004 06:50:02 -0800
Message-ID: <12a479ae.0402100650.de612ba@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,

Theodor Received on Tue Feb 10 2004 - 08:50:02 CST