Обсуждение: Complex outer joins?

Hi,

I've got PsotgreSQL 7.3.
My problem is joins. I've seen the syntax on joins and have sucessefully
used SQLs with joins, but I've got some other big SQL statements using many
and complexjoins. 
Simplified example:select G.SELID, G.TEXT,     L.ID as SELLEVELID , L.SELLEVEL, L.LEVELJOIN, L.LEVELTEXT,    C.ID as
KRITERIENFELDID,C.SELFLD from  G,  L,  C  where     and G.SELID = L.SELID (+)     and L.SELID = C.SELID (+)     and
L.SELLEVEL= C.SELLEVEL (+) 
 
How can i write this in Postgres? As the problem is that I use one sametable for varios joins. This is a problem in
Postgres.Any ideas?When I use the same table in say 3 or 4 joins in one SQL, is the onlysolution unions?? Adn if so,
theperformance will certainly suffer?
 

thanks!
     Carla 




This e-mail and any attachment is for authorised use by the intended recipient(s) only.  It may contain proprietary
material,confidential information and/or be subject to legal privilege.  It should not be copied, disclosed to,
retainedor used by, any other party.  If you are not an intended recipient then please promptly delete this e-mail and
anyattachment and all copies and inform the sender.  Thank you.
 

On Mon, 24 Mar 2003, Correia, Carla wrote:

> 
> Hi,
> 
> I've got PsotgreSQL 7.3.
> 
>  My problem is joins. I've seen the syntax on joins and have sucessefully
> used SQLs with joins, but I've got some other big SQL statements using many
> and complex
>  joins. 
> Simplified example:
>  
>  select G.SELID, G.TEXT, 
>      L.ID as SELLEVELID , L.SELLEVEL, L.LEVELJOIN, L.LEVELTEXT,
>      C.ID as KRITERIENFELDID, C.SELFLD
>   from  G,  L,  C 
>   where 
>      and G.SELID = L.SELID (+) 
>      and L.SELID = C.SELID (+) 
>      and L.SELLEVEL = C.SELLEVEL (+) 
>  
> How can i write this in Postgres? As the problem is that I use one same
>  table for varios joins. This is a problem in Postgres. Any ideas?
>  
>  When I use the same table in say 3 or 4 joins in one SQL, is the only
>  solution unions?? Adn if so, the performance will certainly suffer?
> I don't quite understand you question. but I presume that G, L, 
and C are in fact the same table. I'm not sure what you mean by the (+) 
more clarity is really required.If however G, L, and C are the same table this query can be 
rewritten as 

select G.SELID, G.TEXT,      L.ID as SELLEVELID , L.SELLEVEL, L.LEVELJOIN, L.LEVELTEXT,      C.ID as KRITERIENFELDID,
C.SELFLD from  table as G,  table as L,  table as C  where      and G.SELID = L.SELID       and L.SELID = C.SELID
andL.SELLEVEL = C.SELLEVEL;
 
Where table is the name of the table....I hope that helps

Peter Childs