LEFT OUTER JOIN issue - Mailing list pgsql-sql
| From | Thomas BOURIMECH |
|---|---|
| Subject | LEFT OUTER JOIN issue |
| Date | |
| Msg-id | 5AF59BAE41BBA14090A31CE077580C53238EAB6FD5@EXCHANGE2007.netcenter.local Whole thread Raw |
| Responses |
Re: LEFT OUTER JOIN issue
Re: LEFT OUTER JOIN issue |
| List | pgsql-sql |
<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>