Let join syntax

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 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

worked correctly

Thanks.

> -----Message d'origine-----
> De : Manfred Koizar [mailto:mkoi-pg(at)aon(dot)at]
> Envoye : lundi 16 juin 2003 16:40
> A : Nicolas JOUANIN
> Cc : pgsql-sql(at)postgresql(dot)org
> Objet : Re: [SQL] Let join syntax
>
>
> On Mon, 16 Jun 2003 12:34:34 +0200, "Nicolas JOUANIN"
> <n(dot)jouanin(at)regie-france(dot)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