From i.hasenoehrl@aon.at Thu Mar 28 13:01:09 2002 Received: from email03.aon.at (WARSL401PIP2.highway.telekom.at [195.3.96.74]) by postgresql.org (Postfix) with SMTP id 1ED0D47594C for ; Thu, 28 Mar 2002 08:01:09 -0500 (EST) Received: (qmail 420754 invoked from network); 28 Mar 2002 13:01:10 -0000 Received: from n653p025.adsl.highway.telekom.at (HELO irina) ([62.47.25.153]) (envelope-sender ) by qmail3rs.highway.telekom.at (qmail-ldap-1.03) with SMTP for ; 28 Mar 2002 13:01:10 -0000 Message-ID: <002b01c1d658$4612fc40$01011eac@irina> From: =?iso-8859-1?Q?DI_Hasen=F6hrl?= To: Subject: Left outer join with WHERE clause? Date: Thu, 28 Mar 2002 13:58:36 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0028_01C1D660.A7ACAAC0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200203/375 X-Sequence-Number: 7249 This is a multi-part message in MIME format. ------=_NextPart_000_0028_01C1D660.A7ACAAC0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, I have a difficult query with some INNER JOINs and LEFT JOINs. My first questions is: Is it possible to use a WHERE clause in a LEFT JOIN = or can I realize this in a SUBSELECT and my second question is: please, can anyone show me the correct syntax for th= is ;-) my example: SELECT artikel.a_nr, artikel.kl_ean, artikel.kl_nr, artikel.a_aktiv, artike= l.a_bez1,artikel.a_bez2,artikel.a_bez3,=20 artikel.wg_nr, artikel.ws_nr, artikel.a_vol,vartikelws.ws_bez,varti= kelws.farbe_bez,warengruppebez.wg_bez, wa= rengruppebez.sp_id, produktgruppebez.pg_bez, produktgruppebez.sp_id, kontin= gent.sortiment, kontingent.k_ep, kontingent.k_aufproz, kontingent.k_aufwert,artikel= .a_pfand,kontingent.k_vk, warengruppe.pg_id FROM (= ((((artikel INNER JOIN warengruppe ON artikel.wg_nr =3D warengruppe.wg_nr) INNER JOIN warengruppebez ON warengruppe.wg_nr =3D wareng= ruppebez.wg_nr)=20 LEFT JOIN vartikelws ON artikel.ws_nr =3D vartikelws.ws_n= r) INNER JOIN kontingent ON artikel.a_nr =3D kontingent.a_nr) LEFT JOIN (SELECT lief_nr,kl_kurzbez FROM vartikelliefera= ntbez=20 WHERE ((f_nr)=3D1) ON artikel.a_nr =3D vartikellieferantb= ez.a_nr ) *this does not work INNER JOIN produktgruppebez ON warengruppe.pg_id =3D prod= uktgruppebez.pg_id=20 WHERE (((warengruppebez.sp_id)=3D'D') AND ((produktgruppe= bez.sp_id)=3D'D')=20 AND ((kontingent.sortiment)=3D'2001') AND ((artikel.ws_nr= )=3D1)); =20=20=20=20=20=20 It doesn't work too:=20=20=20=20=20=20=20=20=20=20=20=20 LEFT JOIN (vartikellieferantbez ON artikel.a_nr =3D vartikellieferantb= ez.a_nr WHERE ((vartikellieferantbez.f_nr)=3D1)=20 Please can anyone give me a hint. Many thanks in advance Irina ------=_NextPart_000_0028_01C1D660.A7ACAAC0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
 
Hi all,
 
I have a difficult query with some INNER J= OINs and=20 LEFT JOINs.
My first questions is: Is it possible to u= se a=20 WHERE clause in a LEFT JOIN or can I realize this in a SUBSELECT=20 and
my second question is: please, can anyone = show me=20 the correct syntax for this ;-)
 
my example:
SELECT artikel.a_nr, artikel.kl_ean, artik= el.kl_nr,=20 artikel.a_aktiv,=20 artikel.a_bez1,artikel.a_bez2,artikel.a_bez3, 
       =20 artikel.wg_nr,=20 artikel.ws_nr, artikel.a_vol,vartikelws.ws_bez,vartikelws.farbe_bez,wa= rengruppebez.wg_bez,         = ; =20                = =20         warengruppebez.sp_id,=20 produktgruppebez.pg_bez, produktgruppebez.sp_id,=20 kontingent.sortiment,
       = =20 kontingent.k_ep, kontingent.k_aufproz,=20 kontingent.k_aufwert,artikel.a_pfand,kontingent.k_vk,=20 warengruppe.pg_id         &nbs= p;       =20         FROM (((((artikel INNER JOIN warengru= ppe=20 ON artikel.wg_nr =3D warengruppe.wg_nr)
           =       =20 INNER JOIN warengruppebez ON warengruppe.wg_nr =3D=20 warengruppebez.wg_nr) 
           =       =20 LEFT JOIN vartikelws ON artikel.ws_nr =3D vartikelws.ws_nr)
           =       =20 INNER JOIN kontingent ON artikel.a_nr =3D kontingent.a_nr)
         &nbs= p;       =20 LEFT JOIN (SELECT lief_nr,kl_kurzbez FROM vartikellieferantbez=20
         &nbs= p;        WHERE=20 ((f_nr)=3D1) ON artikel.a_nr =3D vartikellieferantbez.a_nr=20 )             *= this=20 does not work
 
           =       =20 INNER JOIN produktgruppebez ON warengruppe.pg_id =3D=20 produktgruppebez.pg_id 
           =       =20 WHERE (((warengruppebez.sp_id)=3D'D') AND=20 ((produktgruppebez.sp_id)=3D'D') 
           =       =20 AND ((kontingent.sortiment)=3D'2001') AND=20 ((artikel.ws_nr)=3D1));
      
It doesn't work=20 too:            = ;
    =20 LEFT JOIN (vartikellieferantbez ON artikel.a_nr =3D=20 vartikellieferantbez.a_nr WHERE ((vartikellieferantbez.f_nr)=3D1)= =20
 
Please can anyone give me a hint.
Many thanks in advance
Irina
 
 
 
 
 
------=_NextPart_000_0028_01C1D660.A7ACAAC0-- From match@langille.org Thu Mar 28 13:10:19 2002 Received: from bast.unixathome.org (bast.unixathome.org [216.187.105.150]) by postgresql.org (Postfix) with ESMTP id CE51947594C for ; Thu, 28 Mar 2002 08:10:19 -0500 (EST) Received: from wocker (wocker.unixathome.org [192.168.0.99]) by bast.unixathome.org (Postfix) with ESMTP id 481C03F2D; Thu, 28 Mar 2002 08:09:50 -0500 (EST) From: "Dan Langille" Organization: DVL Software Limited To: "=?ISO-8859-1?Q?DI_Hasen=F6hrl?=" Date: Thu, 28 Mar 2002 08:10:21 -0500 MIME-Version: 1.0 Subject: Re: Left outer join with WHERE clause? Reply-To: match@langille.org Cc: In-reply-to: <002b01c1d658$4612fc40$01011eac@irina> X-mailer: Pegasus Mail for Windows (v4.01) Content-type: text/plain; charset=ISO-8859-1 Content-transfer-encoding: Quoted-printable Content-description: Mail message body Message-Id: <20020328130950.481C03F2D@bast.unixathome.org> X-Archive-Number: 200204/21 X-Sequence-Number: 7286 On 28 Mar 2002 at 13:58, DI Hasen=F6hrl wrote: > It doesn't work too: LEFT JOIN (vartikellieferantbez ON > artikel.a_nr =3D vartikellieferantbez.a_nr WHERE > ((vartikellieferantbez.f_nr)=3D1) Your "where" clause for an OUTER JOIN is actually the ON statement. So do = this: LEFT JOIN (vartikellieferantbez ON (artikel.a_nr =3D vartikellieferantbez.a= _nr AND vartikellieferantbez.f_nr =3D 1)) From dan@langille.org Thu Mar 28 13:21:51 2002 Received: from bast.unixathome.org (bast.unixathome.org [216.187.105.150]) by postgresql.org (Postfix) with ESMTP id D212847594C for ; Thu, 28 Mar 2002 08:21:51 -0500 (EST) Received: from wocker (wocker.unixathome.org [192.168.0.99]) by bast.unixathome.org (Postfix) with ESMTP id 22CCF3F2D; Thu, 28 Mar 2002 08:21:23 -0500 (EST) From: "Dan Langille" Organization: DVL Software Limited To: "=?ISO-8859-1?Q?DI_Hasen=F6hrl?=" Date: Thu, 28 Mar 2002 08:21:52 -0500 MIME-Version: 1.0 Subject: Re: Left outer join with WHERE clause? Reply-To: dan@langille.org Cc: In-reply-to: <002b01c1d658$4612fc40$01011eac@irina> X-mailer: Pegasus Mail for Windows (v4.01) Content-type: text/plain; charset=ISO-8859-1 Content-transfer-encoding: Quoted-printable Content-description: Mail message body Message-Id: <20020328132123.22CCF3F2D@bast.unixathome.org> X-Archive-Number: 200203/376 X-Sequence-Number: 7250 [resent with a valid FROM: address] On 28 Mar 2002 at 13:58, DI Hasen=F6hrl wrote: > It doesn't work too: LEFT JOIN (vartikellieferantbez ON > artikel.a_nr =3D vartikellieferantbez.a_nr WHERE > ((vartikellieferantbez.f_nr)=3D1) Your "where" clause for an OUTER JOIN is actually the ON statement. So do this: LEFT JOIN (vartikellieferantbez ON (artikel.a_nr =3D vartikellieferantbez.a_nr AND vartikellieferantbez.f_nr =3D 1)) -- Dan Langille The FreeBSD Diary - http://freebsddiary.org/ - practical examples From i.hasenoehrl@aon.at Thu Mar 28 13:54:27 2002 Received: from email01.aon.at (WARSL401PIP2.highway.telekom.at [195.3.96.74]) by postgresql.org (Postfix) with SMTP id E2261475968 for ; Thu, 28 Mar 2002 08:54:27 -0500 (EST) Received: (qmail 579196 invoked from network); 28 Mar 2002 13:54:30 -0000 Received: from n653p025.adsl.highway.telekom.at (HELO irina) ([62.47.25.153]) (envelope-sender ) by qmail1rs.highway.telekom.at (qmail-ldap-1.03) with SMTP for ; 28 Mar 2002 13:54:30 -0000 Message-ID: <003e01c1d65f$b9a01d80$01011eac@irina> From: =?iso-8859-1?Q?DI_Hasen=F6hrl?= To: Cc: References: <20020328130950.481C03F2D@bast.unixathome.org> Subject: Re: Left outer join with WHERE clause? Date: Thu, 28 Mar 2002 14:51:56 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_003B_01C1D668.1B216200" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200203/377 X-Sequence-Number: 7251 This is a multi-part message in MIME format. ------=_NextPart_000_003B_01C1D668.1B216200 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thank you very much, for your advice, but something is still wrong, because= it doesn't work. I get the following message: parse error at or near "on" I tried the Left Join with an easier query, but it still doesn't work. Mayb= e you see, what I'm making wrong select artikel.a_nr,artikel.a_bez1,artikel.a_bez2 from Artikel=20 LEFT JOIN (vartikellieferantbez ON (artikel.a_nr =3D vartikellieferantbez= .a_nr AND vartikellieferantbez.f_nr=3D1 )); Many thanks, Irina ----- Original Message -----=20 From: Dan Langille=20 To: DI Hasen=F6hrl=20 Cc: pgsql-sql@postgresql.org=20 Sent: Thursday, March 28, 2002 2:10 PM Subject: Re: [SQL] Left outer join with WHERE clause? On 28 Mar 2002 at 13:58, DI Hasen=F6hrl wrote: > It doesn't work too: LEFT JOIN (vartikellieferantbez ON > artikel.a_nr =3D vartikellieferantbez.a_nr WHERE > ((vartikellieferantbez.f_nr)=3D1)=20 Your "where" clause for an OUTER JOIN is actually the ON statement. So d= o this:=20=20 LEFT JOIN (vartikellieferantbez ON (artikel.a_nr =3D vartikellieferantbez= .a_nr AND=20 vartikellieferantbez.f_nr =3D 1))=20=20 ------=_NextPart_000_003B_01C1D668.1B216200 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Thank you very much, for your advice, but = something=20 is still wrong, because it doesn't work.
 
I get the following message: parse= error at=20 or near "on"
 
I tried the Left Join with an easier query= , but it=20 still doesn't work. Maybe you see, what I'm making wrong
select artikel.a_nr,artikel.a_bez1,artik= el.a_bez2=20 from Artikel
LEFT JOIN (vartikellieferantbez ON (artikel.a_nr =3D=20 vartikellieferantbez.a_nr AND vartikellieferantbez.f_nr=3D1 ));
 
Many thanks,
Irina
----- Original Message -----
Fro= m:=20 Dan Lang= ille=20
Cc: pgsql-sql@postgresql.org
Sent: Thursday, March 28, 2002 2:1= 0=20 PM
Subject: Re: [SQL] Left outer join= with=20 WHERE clause?

On 28 Mar 2002 at 13:58, DI Hasen=F6hrl wrote:

>= It=20 doesn't work=20 too:            LE= FT=20 JOIN (vartikellieferantbez ON
> artikel.a_nr =3D vartikellieferantb= ez.a_nr=20 WHERE
> ((vartikellieferantbez.f_nr)=3D1)

Your "where" clau= se for=20 an OUTER JOIN is actually the ON statement.  So do this: =20

LEFT JOIN (vartikellieferantbez ON (artikel.a_nr =3D=20 vartikellieferantbez.a_nr  AND
vartikellieferantbez.f_nr =3D 1))=  =20

------=_NextPart_000_003B_01C1D668.1B216200-- From i.hasenoehrl@aon.at Thu Mar 28 15:33:40 2002 Received: from email04.aon.at (WARSL401PIP2.highway.telekom.at [195.3.96.74]) by postgresql.org (Postfix) with SMTP id 9F9ED4758F1 for ; Thu, 28 Mar 2002 10:33:40 -0500 (EST) Received: (qmail 446310 invoked from network); 28 Mar 2002 15:33:44 -0000 Received: from n653p025.adsl.highway.telekom.at (HELO irina) ([62.47.25.153]) (envelope-sender ) by 172.18.5.232 (qmail-ldap-1.03) with SMTP for ; 28 Mar 2002 15:33:44 -0000 Message-ID: <005e01c1d66d$964ea3c0$01011eac@irina> From: =?iso-8859-1?Q?DI_Hasen=F6hrl?= To: Cc: References: <20020328130950.481C03F2D@bast.unixathome.org> Subject: Re: Left outer join with WHERE clause? Date: Thu, 28 Mar 2002 16:31:10 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_005B_01C1D675.F7E9D8E0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200203/378 X-Sequence-Number: 7252 This is a multi-part message in MIME format. ------=_NextPart_000_005B_01C1D675.F7E9D8E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Sorry, I found my mistake. It was an incorrect parenthesis. Thanks again Irina ----- Original Message -----=20 From: Dan Langille=20 To: DI Hasen=F6hrl=20 Cc: pgsql-sql@postgresql.org=20 Sent: Thursday, March 28, 2002 2:10 PM Subject: Re: [SQL] Left outer join with WHERE clause? On 28 Mar 2002 at 13:58, DI Hasen=F6hrl wrote: > It doesn't work too: LEFT JOIN (vartikellieferantbez ON > artikel.a_nr =3D vartikellieferantbez.a_nr WHERE > ((vartikellieferantbez.f_nr)=3D1)=20 Your "where" clause for an OUTER JOIN is actually the ON statement. So d= o this:=20=20 LEFT JOIN (vartikellieferantbez ON (artikel.a_nr =3D vartikellieferantbez= .a_nr AND=20 vartikellieferantbez.f_nr =3D 1))=20=20 ------=_NextPart_000_005B_01C1D675.F7E9D8E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Sorry, I found my mistake. It was an incor= rect=20 parenthesis.
 
Thanks again
Irina
----- Original Message -----
Fro= m:=20 Dan Lang= ille=20
Cc: pgsql-sql@postgresql.org
Sent: Thursday, March 28, 2002 2:1= 0=20 PM
Subject: Re: [SQL] Left outer join= with=20 WHERE clause?

On 28 Mar 2002 at 13:58, DI Hasen=F6hrl wrote:

>= It=20 doesn't work=20 too:            LE= FT=20 JOIN (vartikellieferantbez ON
> artikel.a_nr =3D vartikellieferantb= ez.a_nr=20 WHERE
> ((vartikellieferantbez.f_nr)=3D1)

Your "where" clau= se for=20 an OUTER JOIN is actually the ON statement.  So do this: =20

LEFT JOIN (vartikellieferantbez ON (artikel.a_nr =3D=20 vartikellieferantbez.a_nr  AND
vartikellieferantbez.f_nr =3D 1))=  =20

------=_NextPart_000_005B_01C1D675.F7E9D8E0-- From josh@agliodbs.com Thu Mar 28 18:36:22 2002 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (Postfix) with ESMTP id C5A2A4759C3 for ; Thu, 28 Mar 2002 13:36:22 -0500 (EST) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.2) with HTTP id 841608; Thu, 28 Mar 2002 10:36:20 -0800 From: "Josh Berkus" Subject: Re: Left outer join with WHERE clause? To: DI =?ISO-8859-1?Q?Hasen=F6hrl?= , Cc: X-Mailer: CommuniGate Pro Web Mailer v.3.5.2 Date: Thu, 28 Mar 2002 10:36:20 -0800 Message-ID: In-Reply-To: <003e01c1d65f$b9a01d80$01011eac@irina> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200203/382 X-Sequence-Number: 7256 DI, > I tried the Left Join with an easier query, but it still doesn't > work. Maybe you see, what I'm making wrong > select artikel.a_nr,artikel.a_bez1,artikel.a_bez2 from Artikel > LEFT JOIN (vartikellieferantbez ON (artikel.a_nr = > vartikellieferantbez.a_nr AND vartikellieferantbez.f_nr=1 )); Get rid of the extra parentheses: select artikel.a_nr,artikel.a_bez1,artikel.a_bez2 from Artikel LEFT JOIN vartikellieferantbez ON (artikel.a_nr = vartikellieferantbez.a_nr AND vartikellieferantbez.f_nr=1 ); -Josh Berkus