Thread: LEFT OUTER JOIN issue
<div class="Section1"><p class="MsoNormal"><span lang="EN-US">Hi everyone, here is my problem : </span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">I got two tables :</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">CREATE TABLE
"public"."calendar_temp"(</span><p class="MsoNormal"><span lang="EN-US"> "id" SERIAL, </span><p
class="MsoNormal"><spanlang="EN-US"> "dat" DATE, </span><p class="MsoNormal"><span lang="EN-US"> "heur" TIME WITHOUT
TIMEZONE, </span><p class="MsoNormal"><span lang="EN-US"> CONSTRAINT "calendar_temp_pkey" PRIMARY KEY("id")</span><p
class="MsoNormal"><spanlang="EN-US">) WITHOUT OIDS;</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">CREATE TABLE "public"."h_part"
(</span><pclass="MsoNormal"><span lang="EN-US"> "idh" SERIAL, </span><p class="MsoNormal"><span lang="EN-US">
"poste_idposte"INTEGER NOT NULL, </span><p class="MsoNormal"><span lang="EN-US"> "t" NUMERIC(4,1), </span><p
class="MsoNormal"><spanlang="EN-US"> "heuremesure" TIME WITHOUT TIME ZONE, </span><p class="MsoNormal"><span
lang="EN-US"> </span>"datmesure" DATE, <p class="MsoNormal"> CONSTRAINT "h_part_datmesure_key" UNIQUE("datmesure",
"heuremesure","poste_idposte"), <p class="MsoNormal"> <span lang="EN-US">CONSTRAINT "h_part_pkey" PRIMARY KEY("idh"),
</span><pclass="MsoNormal"><span lang="EN-US"> CONSTRAINT "h_part_fk" FOREIGN KEY ("poste_idposte")</span><p
class="MsoNormal"><spanlang="EN-US"> REFERENCES "public"."poste"("idposte")</span><p class="MsoNormal"><span
lang="EN-US"> ON DELETE NO ACTION</span><p class="MsoNormal"><span lang="EN-US"> ON UPDATE NO ACTION</span><p
class="MsoNormal"><spanlang="EN-US"> NOT DEFERRABLE</span><p class="MsoNormal"><span lang="EN-US">) WITHOUT
OIDS;</span><pclass="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US">Data in table are like this :</span><p class="MsoNormal"><span
lang="EN-US"> </span><pclass="MsoNormal">calendar_temp<p class="MsoNormal">-----------------<p
class="MsoNormal">id dat heur<p class="MsoNormal">1 15/03/2008
0:00<pclass="MsoNormal">2 15/03/2008 3:00<p class="MsoNormal">3 15/03/2008
6:00<pclass="MsoNormal">4 15/03/2008 9:00<p class="MsoNormal">5 15/03/2008
12:00<pclass="MsoNormal">6 15/03/2008 15:00<p class="MsoNormal"> <p class="MsoNormal">h_part<p
class="MsoNormal">-----------------<pclass="MsoNormal">idh poste_idposte t heuremesure
datmesure<pclass="MsoNormal"><span lang="EN-US">5001 275 8,3
0:00 15/03/2008</span><p class="MsoNormal"><span lang="EN-US">5002 275
12 3:00 15/03/2008</span><p class="MsoNormal"><span lang="EN-US">5003
275 15 6:00 15/03/2008</span><p class="MsoNormal"><span
lang="EN-US">5004 275 18 9:00 15/03/2008</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">I expect the following data set as
aresult from the following request : </span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
lang="EN-US">SELECTct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.t FROM calendar_temp as ct</span><p
class="MsoNormal"><spanlang="EN-US">LEFT OUTER JOIN h_part as hp</span><p class="MsoNormal">ON ct.dat = hp.datmesure<p
class="MsoNormal">ABDct.heur = hp.heuremesure<p class="MsoNormal"><span lang="EN-US">WHERE</span><p
class="MsoNormal"><spanlang="EN-US">hp.poste_idposte = 275</span><p class="MsoNormal"><span lang="EN-US">ORDER BY
ct.dat,ct.heur</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal">dat
heur datmesure heuremesure t<p class="MsoNormal"><span
lang="EN-US">-----------------------------------------------------------</span><pclass="MsoNormal"><span
lang="EN-US">15/03/2008 0:00 15/03/2008 0:00 8,3</span><p
class="MsoNormal"><spanlang="EN-US">15/03/2008 3:00 15/03/2008 3:00
12</span><pclass="MsoNormal"><span lang="EN-US">15/03/2008 6:00 15/03/2008
6:00 15</span><p class="MsoNormal"><span lang="EN-US">15/03/2008 9:00
15/03/2008 9:00 18</span><p class="MsoNormal"><span lang="EN-US">15/03/2008
12:00 null null null</span><p class="MsoNormal"><span
lang="EN-US">15/03/2008 15:00 null null null</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
lang="EN-US">Butunfortunatly all that I get is this set : </span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal">dat heur datmesure heuremesure t<p
class="MsoNormal">-----------------------------------------------------------<pclass="MsoNormal"><span
lang="EN-US">15/03/2008 0:00 15/03/2008 0:00 8,3</span><p
class="MsoNormal"><spanlang="EN-US">15/03/2008 3:00 15/03/2008 3:00
12</span><pclass="MsoNormal"><span lang="EN-US">15/03/2008 6:00 15/03/2008
6:00 15</span><p class="MsoNormal"><span lang="EN-US">15/03/2008 9:00
15/03/2008 9:00 18</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">Getting mad with it...</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">Thanks for any help...</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> </span></div>
<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top">--- On <b>Wed, 4/21/10,
ThomasBOURIMECH <i><thomas.bourimech@metnext.com></i></b> wrote:<br /><blockquote style="border-left: 2px solid
rgb(16,16, 255); margin-left: 5px; padding-left: 5px;"><br />From: Thomas BOURIMECH
<thomas.bourimech@metnext.com><br/>Subject: [SQL] LEFT OUTER JOIN issue<br />To: "'pgsql-sql@postgresql.org'"
<pgsql-sql@postgresql.org><br/>Date: Wednesday, April 21, 2010, 9:06 AM<br /><br /><div
id="yiv1884002683"><style>
<!--
#yiv1884002683 _filtered #yiv1884002683 {font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;}_filtered
#yiv1884002683{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}
#yiv1884002683
#yiv1884002683 p.MsoNormal, #yiv1884002683 li.MsoNormal, #yiv1884002683
div.MsoNormal{margin:0cm;margin-bottom:.0001pt;font-size:11.0pt;font-family:"sans-serif";}
#yiv1884002683 a:link, #yiv1884002683 span.MsoHyperlink{color:blue;text-decoration:underline;}
#yiv1884002683 a:visited, #yiv1884002683 span.MsoHyperlinkFollowed{color:purple;text-decoration:underline;}
#yiv1884002683 span.EmailStyle17{font-family:"sans-serif";color:windowtext;}
#yiv1884002683 .MsoChpDefault{}_filtered #yiv1884002683 {margin:70.85pt 70.85pt 70.85pt 70.85pt;}
#yiv1884002683 div.Section1{}
-->
</style><div class="Section1"><p class="MsoNormal"><span lang="EN-US">Hi everyone, here is my problem : </span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">I got two tables :</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">CREATE TABLE
"public"."calendar_temp"(</span><p class="MsoNormal"><span lang="EN-US"> "id" SERIAL, </span><p
class="MsoNormal"><spanlang="EN-US"> "dat" DATE, </span><p class="MsoNormal"><span lang="EN-US"> "heur" TIME WITHOUT
TIMEZONE, </span><p class="MsoNormal"><span lang="EN-US"> CONSTRAINT "calendar_temp_pkey" PRIMARY KEY("id")</span><p
class="MsoNormal"><spanlang="EN-US">) WITHOUT OIDS;</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">CREATE TABLE "public"."h_part"
(</span><pclass="MsoNormal"><span lang="EN-US"> "idh" SERIAL, </span><p class="MsoNormal"><span lang="EN-US">
"poste_idposte"INTEGER NOT NULL, </span><p class="MsoNormal"><span lang="EN-US"> "t" NUMERIC(4,1), </span><p
class="MsoNormal"><spanlang="EN-US"> "heuremesure" TIME WITHOUT TIME ZONE, </span><p class="MsoNormal"><span
lang="EN-US"> </span>"datmesure" DATE, <p class="MsoNormal"> CONSTRAINT "h_part_datmesure_key" UNIQUE("datmesure",
"heuremesure","poste_idposte"), <p class="MsoNormal"> <span lang="EN-US">CONSTRAINT "h_part_pkey" PRIMARY KEY("idh"),
</span><pclass="MsoNormal"><span lang="EN-US"> CONSTRAINT "h_part_fk" FOREIGN KEY ("poste_idposte")</span><p
class="MsoNormal"><spanlang="EN-US"> REFERENCES "public"."poste"("idposte")</span><p class="MsoNormal"><span
lang="EN-US"> ON DELETE NO ACTION</span><p class="MsoNormal"><span lang="EN-US"> ON UPDATE NO ACTION</span><p
class="MsoNormal"><spanlang="EN-US"> NOT DEFERRABLE</span><p class="MsoNormal"><span lang="EN-US">) WITHOUT
OIDS;</span><pclass="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US">Data in table are like this :</span><p class="MsoNormal"><span lang="EN-US">
</span><pclass="MsoNormal">calendar_temp<p class="MsoNormal">-----------------<p class="MsoNormal">id
dat heur<p class="MsoNormal">1 15/03/2008 0:00<p
class="MsoNormal">2 15/03/2008 3:00<p class="MsoNormal">3 15/03/2008 6:00<p
class="MsoNormal">4 15/03/2008 9:00<p class="MsoNormal">5 15/03/2008 12:00<p
class="MsoNormal">6 15/03/2008 15:00<p class="MsoNormal"> <p class="MsoNormal">h_part<p
class="MsoNormal">-----------------<pclass="MsoNormal">idh poste_idposte t heuremesure
datmesure<pclass="MsoNormal"><span lang="EN-US">5001 275 8,3
0:00 15/03/2008</span><p class="MsoNormal"><span lang="EN-US">5002 275
12 3:00 15/03/2008</span><p class="MsoNormal"><span lang="EN-US">5003
275 15 6:00 15/03/2008</span><p class="MsoNormal"><span
lang="EN-US">5004 275 18 9:00 15/03/2008</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">I expect the following data set as
aresult from the following request : </span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span
lang="EN-US">SELECTct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.t FROM calendar_temp as ct</span><p
class="MsoNormal"><spanlang="EN-US">LEFT OUTER JOIN h_part as hp</span><p class="MsoNormal">ON ct.dat = hp.datmesure<p
class="MsoNormal">ABDct.heur = hp.heuremesure<p class="MsoNormal"><span lang="EN-US">WHERE</span><p
class="MsoNormal"><spanlang="EN-US">hp.poste_idposte = 275</span><p class="MsoNormal"><span lang="EN-US">ORDER BY
ct.dat,ct.heur</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal">dat
heur datmesure heuremesure t<p class="MsoNormal"><span
lang="EN-US">-----------------------------------------------------------</span><pclass="MsoNormal"><span
lang="EN-US">15/03/2008 0:00 15/03/2008 0:00 8,3</span><p
class="MsoNormal"><spanlang="EN-US">15/03/2008 3:00 15/03/2008 3:00
12</span><pclass="MsoNormal"><span lang="EN-US">15/03/2008 6:00 15/03/2008
6:00 15</span><p class="MsoNormal"><span lang="EN-US">15/03/2008 9:00
15/03/2008 9:00 18</span><p class="MsoNormal"><span lang="EN-US">15/03/2008
12:00 null null null</span><p class="MsoNormal"><span
lang="EN-US">15/03/2008 15:00 null null null</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US">But unfortunatly all that I get is this set : </span><p class="MsoNormal"><span
lang="EN-US"> </span><p class="MsoNormal">dat heur datmesure heuremesure t<p
class="MsoNormal">-----------------------------------------------------------<pclass="MsoNormal"><span
lang="EN-US">15/03/2008 0:00 15/03/2008 0:00 8,3</span><p
class="MsoNormal"><spanlang="EN-US">15/03/2008 3:00 15/03/2008 3:00
12</span><pclass="MsoNormal"><span lang="EN-US">15/03/2008 6:00 15/03/2008
6:00 15</span><p class="MsoNormal"><span lang="EN-US">15/03/2008 9:00
15/03/2008 9:00 18</span><p class="MsoNormal"><span lang="EN-US"> </span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">Getting mad with it...</span><p
class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">Thanks for any help...</span><p
class="MsoNormal"><spanlang="EN-US"> </span></div></div></blockquote><div id="yiv1884002683"><div class="Section1"><p
class="MsoNormal"><br/><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">try..</span><p
class="MsoNormal"><spanlang="EN-US"><br /></span><p class="MsoNormal"><span lang="EN-US">SELECT <br /></span><p
class="MsoNormal"><spanlang="EN-US"> ct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.t <br /></span><p
class="MsoNormal"><spanlang="EN-US">FROM <br /></span><p class="MsoNormal"><span lang="EN-US"> calendar_temp as
ct</span><pclass="MsoNormal"><span lang="EN-US">LEFT JOIN <br /></span><p class="MsoNormal"><span lang="EN-US">
h_partas hp</span> ON (ct.dat = hp.datmesure and ct.heur = hp.heuremesure)<p class="MsoNormal"><span
lang="EN-US">WHERE</span><pclass="MsoNormal"><span lang="EN-US"> hp.poste_idposte = 275</span><p
class="MsoNormal"><spanlang="EN-US">ORDER BY ct.dat, ct.heur</span><p class="MsoNormal"><span lang="EN-US">
</span></div></div><blockquotestyle="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left:
5px;"><divid="yiv1884002683"><div class="Section1"></div></div></blockquote></td></tr></table><br />
Le Wed, 21 Apr 2010 11:06:25 +0200,
Thomas BOURIMECH <thomas.bourimech@metnext.com> a écrit :
> Hi everyone, here is my problem :
>
> I got two tables :
>
> CREATE TABLE "public"."calendar_temp" (
> "id" SERIAL,
> "dat" DATE,
> "heur" TIME WITHOUT TIME ZONE,
> CONSTRAINT "calendar_temp_pkey" PRIMARY KEY("id")
> ) WITHOUT OIDS;
>
>
> 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;
>
>
> Data in table are like this :
>
> 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
>
> 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
>
> I expect the following data set as a result from the following
> request :
>
> 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 = hp.datmesure
> ABD ct.heur = hp.heuremesure
> WHERE
> hp.poste_idposte = 275
> ORDER BY ct.dat, ct.heur
>
> 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
>
>
> But unfortunatly all that I get is this set :
>
> 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
>
>
> Getting mad with it...
>
> 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.
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 = hp.datmesure
AND ct.heur = hp.heuremesure
WHERE
coalesce(hp.poste_idposte,275) = 275
ORDER BY ct.dat, ct.heur;