Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 4F5CD633BA7 for ; Thu, 22 Apr 2010 04:54:49 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 11313-01 for ; Thu, 22 Apr 2010 07:54:38 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from fw1.nexto.fr (smtp.nexto.fr [85.31.207.125]) by mail.postgresql.org (Postfix) with ESMTP id 627D0633F96 for ; Thu, 22 Apr 2010 04:54:38 -0300 (ADT) Received: (from uucp@localhost) by fw1.nexto.fr id o3M7sYlC007730; Thu, 22 Apr 2010 09:54:34 +0200 Received: from A by B ; Thu, 22 Apr 2010 07:54:28 +0000 Received: from A by B ; Thu, 22 Apr 2010 09:54:27 +0200 From: Thomas BOURIMECH To: "'Oliveiros'" , "pgsql-sql@postgresql.org" , Oliveiros Date: Thu, 22 Apr 2010 09:54:27 +0200 Subject: Re: LEFT OUTER JOIN issue [SOLVED] Thread-Topic: [SQL] LEFT OUTER JOIN issue [SOLVED] Thread-Index: AcrhWG/92DKTxQiVRwujm33CViqHfAAmGHeA Message-ID: <5AF59BAE41BBA14090A31CE077580C53238F854ED1@EXCHANGE2007.netcenter.local> References: <8111AC40AA9840A489285D145B52A2BB@marktestcr.marktest.pt> In-Reply-To: <8111AC40AA9840A489285D145B52A2BB@marktestcr.marktest.pt> Accept-Language: fr-FR Content-Language: fr-FR X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: fr-FR Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-F360-PVer: 0000001 X-F360-Spam: Unknown X-F360-RefID: str=0001.0A0B0201.4BD000B4.018B,ss=1,fgs=0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.8 tagged_above=-10 required=5 tests=BAYES_50=0.8 X-Spam-Level: X-Archive-Number: 201004/85 X-Sequence-Number: 34012 Thankx everybody It worked... -----Message d'origine----- De=A0: Oliveiros [mailto:oliveiros.cristina@marktest.pt]=20 Envoy=E9=A0: mercredi 21 avril 2010 15:42 =C0=A0: Thomas BOURIMECH; pgsql-sql@postgresql.org; Oliveiros Objet=A0: Re: [SQL] LEFT OUTER JOIN issue Try moving the hp.poste_idposte=3D275 inside the LEFT JOIN condition, somet= hin like : =20 SELECT ct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.t FROM calendar_temp a= s ct LEFT OUTER JOIN h_part as hp ON (ct.dat =3D hp.datmesure AND ct.heur =3D hp.heuremesure AND hp.poste_idposte =3D 275) ORDER BY ct.dat, ct.heur =20 And drop the WHERE clause. =20 See if it gives the results you intended. =20 Best, Oliveiros ----- Original Message -----=20 From: Oliveiros =20 To: Thomas BOURIMECH ; pgsql-sql@po= stgresql.org=20 Sent: Wednesday, April 21, 2010 1:53 PM Subject: Re: [SQL] LEFT OUTER JOIN issue Hi, Thomas. =20 I believe it is because of your WHERE clause, which is filtering out the n= ulls from hp table. According to WHERE hp.poste_idposte =3D 275 =20 You only want registers that have hp.poste_idposte =3D 275, not the null o= nes. =20 HTH =20 Best, Oliveiros ----- Original Message -----=20 From: Thomas BOURIMECH =20 To: 'pgsql-sql@postgresql.org'=20 Sent: Wednesday, April 21, 2010 1:29 PM Subject: [SQL] LEFT OUTER JOIN issue Hi everyone, here is my problem :=20 =20 I got two tables : =20 CREATE TABLE "public"."calendar_temp" ( "id" SERIAL,=20 "dat" DATE,=20 "heur" TIME WITHOUT TIME ZONE,=20 CONSTRAINT "calendar_temp_pkey" PRIMARY KEY("id") ) WITHOUT OIDS; =20 =20 CREATE TABLE "public"."h_part" ( "idh" SERIAL,=20 "poste_idposte" INTEGER NOT NULL,=20 "t" NUMERIC(4,1),=20 "heuremesure" TIME WITHOUT TIME ZONE,=20 "datmesure" DATE,=20 CONSTRAINT "h_part_datmesure_key" UNIQUE("datmesure", "heuremesure", "p= oste_idposte"),=20 CONSTRAINT "h_part_pkey" PRIMARY KEY("idh"),=20 CONSTRAINT "h_part_fk" FOREIGN KEY ("poste_idposte") REFERENCES "public"."poste"("idposte") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE ) WITHOUT OIDS; =20 =20 Data in table are like this : =20 calendar_temp ----------------- id dat heur 1 15/03/2008 0:00 2 15/03/2008 3:00 3 15/03/2008 6:00 4 15/03/2008 9:00 5 15/03/2008 12:00 6 15/03/2008 15:00 =20 h_part ----------------- idh poste_idposte t heuremesure datmesure 5001 275 8,3 0:00 = 15/03/2008 5002 275 12 3:00 = 15/03/2008 5003 275 15 6:00 = 15/03/2008 5004 275 18 9:00 = 15/03/2008 =20 I expect the following data set as a result from the following request := =20 =20 SELECT ct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.t FROM calendar_temp= as ct LEFT OUTER JOIN h_part as hp ON ct.dat =3D hp.datmesure AND ct.heur =3D hp.heuremesure WHERE hp.poste_idposte =3D 275 ORDER BY ct.dat, ct.heur =20 dat heur datmesure heuremesure t ----------------------------------------------------------- 15/03/2008 0:00 15/03/2008 0:00 = 8,3 15/03/2008 3:00 15/03/2008 3:00 = 12 15/03/2008 6:00 15/03/2008 6:00 = 15 15/03/2008 9:00 15/03/2008 9:00 = 18 15/03/2008 12:00 null null = null 15/03/2008 15:00 null null = null =20 =20 But unfortunatly all that I get is this set :=20 =20 dat heur datmesure heuremesure t ----------------------------------------------------------- 15/03/2008 0:00 15/03/2008 0:00 = 8,3 15/03/2008 3:00 15/03/2008 3:00 = 12 15/03/2008 6:00 15/03/2008 6:00 = 15 15/03/2008 9:00 15/03/2008 9:00 = 18 =20 =20 Getting mad with it... =20 Thanks for any help... =20 =20 =20 No virus found in this incoming message. Checked by AVG - www.avg.com=20 Version: 9.0.801 / Virus Database: 271.1.1/2811 - Release Date: 04/20/10 22= :14:00