SQL & PL/SQL

I need to create a quite complex SQL report, and I've run into the following problem. Imagine two simple tables - say table_a and table_b:

create table table_a (id integer, primary key (id));

create table table_b (id integer, a_id integer references table_a(id), primary key (id)); 

Say I need to run a query that (a) selects all the rows from table_a, and (b) counts all related rows from table_b. That's something like this

SELECT a.*,
    (SELECT COUNT(*) FROM table_b WHERE a_id = a.id) AS cnt
FROM table_a a

This works fine, but I need to do much more complex operations on table_b (CONNECT BY, etc.) so I need to nest the subselects and move the "a_id = a.id" condition "deeper" - something like this:

SELECT a.*,
    (SELECT COUNT(*) FROM
        (SELECT * FROM table_b WHERE a_id = a.id)
    ) AS cnt
FROM table_a a

Again - this is just a simplified example, I need to perform much more complex processing.

But as it turns out this returns "ORA-00904: invalid identifier" exception, as Oracle suddenly does not know the "a.id" in the inner-most query. How to solve this? How to convince Oracle to use the value from the outer query?

Edited by: TomasVondra on 24.11.2009 3:26