By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,502 Members | 1,878 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,502 IT Pros & Developers. It's quick & easy.

Database query with subselect

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Jul 19 '05 #2

P: n/a
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
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.