X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id 12B6F30EE4C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 16 Jun 2003 14:40:13 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 34633-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 16 Jun 2003 11:40:02 -0300 (ADT)
Received: from email05.aon.at (WARSL402PIP4.highway.telekom.at [195.3.96.79])
	by svr1.postgresql.org (Postfix) with SMTP id 140A530EE39
	for <pgsql-sql@postgresql.org>; Mon, 16 Jun 2003 11:40:02 -0300 (ADT)
Received: (qmail 431110 invoked from network); 16 Jun 2003 14:40:02 -0000
Received: from m149p004.dipool.highway.telekom.at (HELO cantor)
	([62.46.8.132]) (envelope-sender <mkoi-pg@aon.at>)
	by qmail5rs.highway.telekom.at (qmail-ldap-1.03) with SMTP
	for <n.jouanin@regie-france.com>; 16 Jun 2003 14:40:02 -0000
From: Manfred Koizar <mkoi-pg@aon.at>
To: "Nicolas JOUANIN" <n.jouanin@regie-france.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Let join syntax
Date: Mon, 16 Jun 2003 16:39:32 +0200
Message-ID: <8sjrev0b9ut9bcjgruh3p4onpg1d9qlqhd@4ax.com>
References: <CEEJJOCKHCPFNIOMMIDFGECICHAA.n.jouanin@regie-france.com>
In-Reply-To: <CEEJJOCKHCPFNIOMMIDFGECICHAA.n.jouanin@regie-france.com>
X-Mailer: Forte Agent 1.93/32.576 English (American)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Archive-Number: 200306/142
X-Sequence-Number: 13900

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