Re: referring to calculated column in sub select

> Hi,
>
> why column "acoltest" is not found by the subselect in this select:
>
>
> SELECT
>       acol + 100 as acoltest,
>      (select max(t) from mytab where anothercol=acoltest) as col2
> FROM mytab2
> group by somet
> ???
> Only columns belonging to a table can be used in a subselect??? What about "calculated" columns?
> Thank you

Looks like you are trying to do a correlated subquery. You can do them
in both Postgres and MySQL. It needs to be done in the where clause on
postgres but can be used in the select clause on MySQL and Oracle. The
problem with correlated subqueries is that they are executed once for
every row in the outer query, so they are usually quite a bit slower
than doing a join like Sam suggested.

Scott