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

Is it possible to left join based on previous joins result

From
Emi Lu
Date:
Hello,

Is it possible to do something like:

select ...
from t1
inner join t2 ...
left join t2.colN

When t1 inner join with t2 I got unique result for t2.colN( colN's value 
is table name).

Can I continue to left join with the column "colN" in table t2?

Thanks





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

From
Emi Lu
Date:
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.



> 
> Is it possible to do something like:
> 
> select ...
> from t1
> inner join t2 ...
> left join t2.colN
> 
> When t1 inner join with t2 I got unique result for t2.colN( colN's value 
> is table name).
> 
> Can I continue to left join with the column "colN" in table t2?
> 
> Thanks
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>               http://archives.postgresql.org



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

From
Stephan Szabo
Date:
On Thu, 7 Sep 2006, Emi Lu wrote:

> Hello,
>
> Is it possible to do something like:
>
> select ...
> from t1
> inner join t2 ...
> left join t2.colN
>
> When t1 inner join with t2 I got unique result for t2.colN( colN's value
> is table name).
>
> Can I continue to left join with the column "colN" in table t2?

No, in part because it'd have to actually evaluate the first join in order
to even plan the remainder of the query. It might be possible to do
something similar, albeit somewhat slowly, inside a set returning
function, but you'd have to decide how to handle more than one row being
returned from the first join even if the value is unique, is that one join
against the table or multiple joins.


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

From
Ragnar
Date:
On fim, 2006-09-07 at 10:02 -0400, Emi Lu 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.

it seems to me that basically you want to
select from a table whose name is stored in another
table.

one way to do that would be to use plpgsql or
some other procedural language to create
a set returning function, so that you
could do:
SELECT * from selfromtab('sometable');

after that is done,you might be able to use that
in joins

gnari







> 
> 
> > 
> > Is it possible to do something like:
> > 
> > select ...
> > from t1
> > inner join t2 ...
> > left join t2.colN
> > 
> > When t1 inner join with t2 I got unique result for t2.colN( colN's value 
> > is table name).
> > 
> > Can I continue to left join with the column "colN" in table t2?
> > 
> > Thanks
> > 
> > 
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> > 
> >               http://archives.postgresql.org
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 



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

From
"Aaron Bono"
Date:
On 9/7/06, Emi Lu <emilu@encs.concordia.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
==================================================================