Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 6B9B86331E2 for ; Thu, 22 Apr 2010 20:09:31 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 91655-06 for ; Thu, 22 Apr 2010 23:09:13 +0000 (UTC) X-Greylist: delayed 00:07:02.05308 by SQLgrey-1.7.6 Received: from two.asits.eu (ikujam.org [91.121.100.108]) by mail.postgresql.org (Postfix) with ESMTP id 144EE632A8F for ; Thu, 22 Apr 2010 20:09:20 -0300 (ADT) Received: from gentoo-quad (unknown [10.11.100.3]) by two.asits.eu (Postfix) with ESMTPS id C0B2450C10 for ; Fri, 23 Apr 2010 01:00:29 +0200 (CEST) Date: Fri, 23 Apr 2010 01:02:18 +0200 From: Arne =?UTF-8?B?U3TDtmxjaw==?= To: pgsql-sql@postgresql.org Subject: Re: LEFT OUTER JOIN issue Message-ID: <20100423010218.277a383b@gentoo-quad> In-Reply-To: <5AF59BAE41BBA14090A31CE077580C53238EAB6FD5@EXCHANGE2007.netcenter.local> References: <5AF59BAE41BBA14090A31CE077580C53238EAB6FD5@EXCHANGE2007.netcenter.local> Organization: ASITS X-Mailer: Claws Mail 3.7.5 (GTK+ 2.18.9; x86_64-pc-linux-gnu) X-Face: _NZf5%>J, >L(o4d(e?([e2g9'U=Fo{EN&Y* Q:Hh>:^o3OU0r[Q:u-xkCqwVk6; )mb/kf<)OII,\+KZ~*peuAGm/7>D9=n}Zr |Zo"I]^$]ajw/Xj6, 8@=8*?@+g>cOQQ!)v|rt8rHc}5%A}LL!ix+X=Aek%u>)qc_p+TO|, }>"%!kUI PX|dMqn(6tEvW\`g<}(4-g&LwH2Q~d9g<9Y Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.001 tagged_above=-10 required=5 tests=BAYES_40=-0.001 X-Spam-Level: X-Archive-Number: 201004/88 X-Sequence-Number: 34015 Le Wed, 21 Apr 2010 11:06:25 +0200, Thomas BOURIMECH a =C3=A9crit : > Hi everyone, here is my problem : >=20 > I got two tables : >=20 > CREATE TABLE "public"."calendar_temp" ( > "id" SERIAL, > "dat" DATE, > "heur" TIME WITHOUT TIME ZONE, > CONSTRAINT "calendar_temp_pkey" PRIMARY KEY("id") > ) WITHOUT OIDS; >=20 >=20 > CREATE TABLE "public"."h_part" ( > "idh" SERIAL, > "poste_idposte" INTEGER NOT NULL, > "t" NUMERIC(4,1), > "heuremesure" TIME WITHOUT TIME ZONE, > "datmesure" DATE, > CONSTRAINT "h_part_datmesure_key" UNIQUE("datmesure", > "heuremesure", "poste_idposte"), CONSTRAINT "h_part_pkey" PRIMARY > KEY("idh"), 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 > 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 > ABD 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 > 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... you have to consider the where condition. In those lines where you expect null values, he poste_idposte field would be null as well.=20 for example, the following query returns the expected result : 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=20 AND ct.heur =3D hp.heuremesure=20 WHERE coalesce(hp.poste_idposte,275) =3D 275 ORDER BY ct.dat, ct.heur;