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