Re: Is it possible to left join based on previous joins result

On 9/7/06, Emi Lu <emilu(at)encs(dot)concordia(dot)ca> wrote:
>
> I tried the example as the following:
>
> create table a(col1);
> create table b(col1, col2)
>
> select a.*
> from a inner join b using(col2)
> left join b.col2 as c on (c.col1 = a.col1)
>
> System notifies me that b is not a schema name.
>
> So, I guess the approach that I tried to do is not acceptable by Pgsql
> grammar.

the syntax is
LEFT JOIN [table] AS ...

you have b.col2 which means the database will interpret col2 as the table
name and subsequently b as the schema name

You should have
SELECT a.*
FROM a
INNER JOIN b using(col2)
LEFT JOIN b as c on (c.col1 = a.col1)

In the "using(col2)", what columns and tables are you joining there? I
always dislike that syntax as it is ambiguous in some cases and not very
easy to read.

==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================