Re: Subselect AS and Where clause

On 01/26/2011 09:34 AM, Uwe Schroeder wrote:

> Google being useless tonight - now that's new :-)
>
> What I'm trying to do is the following and I'm certain there is a simple
> solution which eludes me:
>
> I have a query like this:
>
> SELECT a,b,c, (select problem from other_table where id=a) as problem FROM
> mytable WHERE a=1
>
> So far so good. Actually "problem" always resolves to one record, so it's not
> the "multiple records returned" problem.
>
> What I try to do is this:
>
> SELECT a,b,c, (select problem from other_table where id=a) as problem FROM
> mytable WHERE a=1 and problem = 3
>
> see the "problem=3" part in the where clause? The error I get is
>
> SQLError: (ProgrammingError) column "problem" does not exist
>
> Do I miss something? Shouldn't the "as" assignment make this virtual column
> available to the where clause? I think this should work and I know it works if
> I'd make a view out of the query. However, the query is built dynamically, so
> turning it into a view isn't really an option.
>
> Any pointer will be greatly appreciated.
>
> Uwe
The simplest solution would be
select a,b,c,problem from mytable left join other_table on id=a
where a=1 and problem=3

column aliases dont work inside the statement itself, neither in the
select, join, where, group nor having clauses in postgresql (they do in
mysql) to get this to work you would need to use the full column such as
select a,b,c,(select problem from test2 where id=a) as prob
from test1
where a=1 and (select problem from test2 where id=a) = 'dont work'

Sim