Problem with count query in column list that joins with main table

Hi,

Please see example below - I am trying to retrieve the count of some rows in a second table as one of the columns of the query along with columns of the first table. - The query works fine with Postgres but not with GridSQL.

I see the following exception in the console log:

The query will work fine if it is a count(*) of some sort without joining with the other table from which we are selecting. i.e. something like "select distinct f1, f2, (select count(*) from t2) as f1cnt from t1" works fine.

Testing this with a GridSQL build from CVS built this morning.

Hope you can help, Thanks,

Ranga

Hi Ranga,

Sorry, but GridSQL does not support correlated subqueries in the SELECT clause.
Hopefully this query will do what you want:

select t1.f1, t1.f2, count(*) from t1, t2 where t2.f1 = t1.f1 group by t1.f1, t1.f2;

Andrei

Hi Andrei,

Thanks for your quick response - I think I can work around the issue using an approach similar to what you suggested - the actual query is a bit more complicated requiring outer joins etc. but the concept you suggested still holds.

Thanks,

Ranga