Thread: SELECT does not find table created by itself

select * FROM (select 1 as a) t2
left join t2 t3 ON TRUE

causes error

ERROR:  relation "t2" does not exist

How to fix so that table t2 is created only once ?

Andrus.

2010/1/2 Andrus <kobruleht2@hot.ee>:
> select * FROM (select 1 as a) t2 left join t2 t3 ON TRUE
>
> causes error
>
> ERROR:  relation "t2" does not exist
>
> How to fix so that table t2 is created only once ?

Not entirely sure if this is what you're looking for, but I think a
CTE can solve your problem:
WITH t2 AS (SELECT 1 AS a)
SELECT * FROM t2 LEFT JOIN t3 ON TRUE



--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Magnus Hagander wrote:
> > select * FROM (select 1 as a) t2 left join t2 t3 ON TRUE
> > causes error
> > ERROR:  relation "t2" does not exist

> Not entirely sure if this is what you're looking for, but I think a
> CTE can solve your problem:
> WITH t2 AS (SELECT 1 AS a)
> SELECT * FROM t2 LEFT JOIN t3 ON TRUE

Shouldn't that be:
WITH t2 AS (SELECT 1 AS a)
SELECT * FROM t2 LEFT JOIN t2 t3 ON TRUE

Yours,
Laurenz Albe