Let join syntax - Mailing list pgsql-sql

Hi,

select x0.coh_agent ,x0.bra_id ,x0.dpr_id ,x0.usr_id ,x0.csc_id   ,x0.spp_id ,x0.csc_id_inv ,x0.coh_doc_inv
,x0.coh_d_inv,   x0.coh_process ,x0.coh_doc_main ,x0.coh_status ,x0.coh_total_local   ,x0.coh_basis_local
,x0.coh_cost_local,x0.coh_profit_local   ,x0.coh_over_local ,x0.coh_com_earned ,x0.coh_com_adjust   ,x0.coh_com_held
,x0.coh_com_cancel,x0.coh_com_topay ,x0.coh_d_paid   ,x0.coh_matchname ,x0.coh_cscmatch ,x0.coh_com_paid ,x3.cur_dec_nb
 ,x3.cur_location ,x3.cur_negative ,x3.cur_dec_char ,x3.cur_group_char   ,x5.sec_id ,x1.cpy_id ,x1.cpy_cr_tr
,x1.cpy_cr_tg,x1.cpy_cr_tb   ,x2.bra_screen ,x2.bra_id ,x2.lng_id ,x4.tad_cpy_name
 
from   coh x0   INNER JOIN bra x2 ON x0.bra_id = x2.bra_id   INNER JOIN tad x4 ON x2.tad_id = x4.tad_id   LEFT  JOIN
secx5 ON x5.thr_id = x4.thr_id                       AND x5.usr_id = x0.usr_id                       AND x5.bra_id =
x0.bra_id                      AND x5.dpr_id = x0.dpr_id,   cpy x1 , cur x3 ,dpr x6
 
where x0.cpy_id = x1.cpy_id
AND x0.cur_id = x3.cur_id
AND x0.dpr_id = x6.dpr_id
AND x2.bra_id = x6.bra_id

worked correctly

Thanks.


> -----Message d'origine-----
> De : Manfred Koizar [mailto:mkoi-pg@aon.at]
> Envoye : lundi 16 juin 2003 16:40
> A : Nicolas JOUANIN
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] Let join syntax
> 
> 
> On Mon, 16 Jun 2003 12:34:34 +0200, "Nicolas JOUANIN"
> <n.jouanin@regie-france.com> wrote:
> >from
> >    coh x0 , cpy x1 ,bra x2 ,
> >    cur x3  ,tad x4 LEFT OUTER JOIN sec x5 on x5.thr_id=x4.thr_id AND
> >x5.usr_id=x0.usr_id AND x5.bra_id = x0.bra_id AND x5.dpr_id = x0.dpr_id,
> >    dpr x6 where ((((((((((x0.cpy_id = x1.cpy_id ) AND (x0.bra_id
> >    = x2.bra_id ) ) AND (x0.cur_id = x3.cur_id ) ) AND (x0.dpr_id
> >    = x6.dpr_id ) ) AND (x2.tad_id = x4.tad_id ) ) AND (x5.thr_id
> >    = x4.thr_id ) ) AND (x2.bra_id = x6.bra_id ) ) AND (x5.usr_id
> >    = x0.usr_id ) ) AND (x5.bra_id = x0.bra_id ) ) AND (x5.dpr_id
> >    = x0.dpr_id )
> 
> Nicolas, sometimes reformatting a query helps a lot:
> 
> FROM
>     coh x0 , cpy x1 ,bra x2 , cur x3  ,
>     tad x4 LEFT OUTER JOIN sec x5 ON
>   x5.thr_id = x4.thr_id AND
>   x5.usr_id = x0.usr_id AND
>   x5.bra_id = x0.bra_id AND
>   x5.dpr_id = x0.dpr_id,
>     dpr x6
> WHERE
>   x0.cpy_id = x1.cpy_id AND
>   x0.bra_id = x2.bra_id AND
>   x0.cur_id = x3.cur_id AND
>   x0.dpr_id = x6.dpr_id AND
>   x2.tad_id = x4.tad_id AND
>   x2.bra_id = x6.bra_id AND
>   x5.thr_id = x4.thr_id AND
>   x5.usr_id = x0.usr_id AND
>   x5.bra_id = x0.bra_id AND
>   x5.dpr_id = x0.dpr_id 
> 
> First note that the last four lines duplicate the ON conditions thus
> effectively turning the OUTER JOIN into an INNER JOIN.  As I suspect
> that that was not your intention, simply omit those four conditions
> from the WHERE clause.
> 
> Now inserting INNER JOIN where the syntax forces us to do so leads to
> (completely untested):
> 
> FROM
>   coh x0
>   INNER JOIN bra x2 ON x0.bra_id = x2.bra_id
>   INNER JOIN tad x4 ON x2.tad_id = x4.tad_id
>   LEFT JOIN sec x5 ON x5.thr_id = x4.thr_id AND
>                       x5.usr_id = x0.usr_id AND
>                       x5.bra_id = x0.bra_id AND
>                       x5.dpr_id = x0.dpr_id,
>   cpy x1, cur x3, dpr x6
> WHERE
>   x0.cpy_id = x1.cpy_id AND
>   x0.cur_id = x3.cur_id AND
>   x0.dpr_id = x6.dpr_id AND
>   x2.bra_id = x6.bra_id
> 
> You might feel like replacing the remaining commas in the FROM clause
> and the corresponding WHERE conditions with semantically equivalent
> INNER JOINs.  But this limits the freedom of the planner which may be
> a good or a bad thing...
> 
> Servus
>  Manfred