Re: LEFT OUTER JOIN issue - Mailing list pgsql-sql

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