X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 043E1D1B556
	for <pgsql-general-postgresql.org@localhost.postgresql.org>;
	Fri, 10 Oct 2003 06:53:49 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 45843-02
	for <pgsql-general-postgresql.org@localhost.postgresql.org>;
	Fri, 10 Oct 2003 03:53:01 -0300 (ADT)
Received: from smtp.lewczuk.com (unknown [195.150.168.118])
	by svr1.postgresql.org (Postfix) with SMTP id EE1A3D1B545
	for <pgsql-general@postgresql.org>;
	Fri, 10 Oct 2003 03:52:58 -0300 (ADT)
Received: (qmail 29781 invoked by uid 700); 10 Oct 2003 06:52:44 -0000
Received: from unknown (HELO marek) (62.233.139.198)
	by 217.96.100.118 with SMTP; 10 Oct 2003 06:52:43 -0000
Reply-To: <newsy@lewczuk.com>
From: "Marek Lewczuk" <newsy@lewczuk.com>
To: "'Karsten Hilbert'" <Karsten.Hilbert@gmx.net>,
	"'Lista dyskusyjna pgsql-general'" <pgsql-general@postgresql.org>,
	"Lista dyskusyjna pgsql-sql" <pgsql-sql@postgresql.org>
Subject: Re: SQL query problem (many table in FROM statement and many LEFT
	JOIN's)
Date: Fri, 10 Oct 2003 08:53:11 +0100
Organization: LEWCZUK.COM
Message-ID: <004001c38f03$8d8b8230$4701a8c0@marek>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.2627
In-Reply-To: <20031009233308.B2022@hermes.hilbert.loc>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Importance: Normal
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=IN_REP_TO,
	QUOTED_EMAIL_TEXT, UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200310/480
X-Sequence-Number: 50553

> > SELECT
> >   _CON.con_id,
> Please make sure you get the quoting right regarding table 
> names. PostgreSQL will fold _CON into _con unless quoted 
> "_CON". So, it may be that you created the table with quotes 
> ("_CON"). Now, in your query you don't use quotes and thusly 
> it is looking for a _con table. The simple rule of thumb is 
> to either always or never use quotes.

I don't think that this is the solution, becouse the query:

SELECT
  _CON.con_id,
  _MOD.mod_ty,
  _CON.dri_id,
  _CON.man_cod,
  _ENG.eng_pow
FROM
  db_data.mda_mod _MOD,
  db_data.mda_mod_con _CON,
  db_data.mda_mak_eng _ENG,
  db_data.set_mda_fue _FUE
WHERE
  _MOD.mod_id = '283' AND
  _CON.mod_id = _MOD.mod_id AND
  _CON.psd <= NOW() AND
  _CON.ped > NOW() AND
  _ENG.eng_id = _CON.eng_id AND
  _ENG.eng_fue = _FUE.fue_id

...is working fine. I belive that this some problem with LEFT JOIN and
FROM statement. If I will rewrite this query:

SELECT
  _CON.con_id,
  _MOD.mod_ty,
  _VER.version,
  _YEA.year,
  _CON.dri_id,
  _CON.man_cod,
  _ENG.eng_pow
FROM
  db_data.mda_mod _MOD
  JOIN db_data.mda_mod_con _CON ON _CON.mod_id = _MOD.mod_id AND
_CON.psd <= NOW() AND _CON.ped > NOW()
  JOIN db_data.mda_mak_eng _ENG ON _ENG.eng_id = _CON.eng_id
  JOIN db_data.set_mda_fue _FUE ON _ENG.eng_fue = _FUE.fue_id
  LEFT JOIN db_data.mda_mod_ver _VER ON _VER.ver_id = _CON.ver_id
  LEFT JOIN db_data.mda_mod_yea _YEA ON _YEA.yea_id = _CON.yea_id 
WHERE
  _MOD.mod_id = '283'


... It also working fine. !!!! The question is, why my first query isn't
working:

SELECT
  _CON.con_id,
  _MOD.mod_ty,
  _VER.version,
  _YEA.year,
  _CON.dri_id,
  _CON.man_cod,
  _ENG.eng_pow
FROM
  db_data.mda_mod _MOD,
  db_data.mda_mod_con _CON,
  db_data.mda_mak_eng _ENG,
  db_data.set_mda_fue _FUE
  LEFT JOIN db_data.mda_mod_ver _VER ON _VER.ver_id = _CON.ver_id
  LEFT JOIN db_data.mda_mod_yea _YEA ON _YEA.yea_id = _CON.yea_id 
WHERE
  _MOD.mod_id = '283' AND
  _CON.mod_id = _MOD.mod_id AND
  _CON.psd <= NOW() AND
  _CON.ped > NOW() AND
  _ENG.eng_id = _CON.eng_id AND
  _ENG.eng_fue = _FUE.fue_id