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

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

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

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.

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
>