Your subquery is not correated, so the following should work:
Your example probably makes more sense if it is correlated. Post the full query code.
Message was edited by: Jochem van Dieten
One cannot do a subquery in a QoQ, so that's your problem.
QoQ is fairly restricted in its SQL support.
It's all documented here:
Thanks for the quick replies. So yes, I was trying to do a sub-query in a QoQ. Since that does not appear to be supported what alternatives are there?
Here is the SQL I'm trying to run against my master query. I think it is correlated?
SELECT DISTINCT c.city, u.state_code, (
SELECT count(*) FROM UserTable
WHERE city_id = u.city_id
) as numProfiles
FROM UserTable u
LEFT JOIN City C on c.city_id = u.city_id
WHERE u.state_code <> ''
Any help appreciated on achieving the net result
Yes, this subquery is correlated (i.e. you have some WHERE in the subquery that tells how rows from the subquery relate to outer rows). The subquery isn't the problem, you can easily flatten that into a join. It is the left join that isn't going to work.
There's an error in that SQL, it should be referencing the master query as opposed to UserTable, in any case, it looks like I can't use subqueries in QofQ so I'm essentially looking for an alternative that may work.
No, I am saying you can flatten your subquery. This will give the same result:
SELECT u.state_code, count(*) as numProfiles
FROM UserTable u,
GROUP BY u.state
What you can't do is a LEFT JOIN against your city table.