Re: 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_idAND 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...

ServusManfred