Обсуждение: 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">ANDct.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">-----------------------------------------------------------<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><p class="MsoNormal"><span
lang="EN-US"> </span></div>
Hi, Thomas.
I believe it is because of your WHERE clause, which is filtering out the nulls from hp table.
According to
WHERE
hp.poste_idposte = 275
You only want registers that have hp.poste_idposte = 275, not the null ones.
HTH
Best,
Oliveiros
----- Original Message -----From: Thomas BOURIMECHSent: Wednesday, April 21, 2010 1:29 PMSubject: [SQL] LEFT OUTER JOIN issueHi 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
AND 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...
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
AND hp.poste_idposte = 275)
ORDER BY ct.dat, ct.heur
And drop the WHERE clause.
See if it gives the results you intended.
Best,
Oliveiros
----- Original Message -----From: OliveirosSent: Wednesday, April 21, 2010 1:53 PMSubject: Re: [SQL] LEFT OUTER JOIN issueHi, Thomas.
I believe it is because of your WHERE clause, which is filtering out the nulls from hp table.
According to
WHERE
hp.poste_idposte = 275
You only want registers that have hp.poste_idposte = 275, not the null ones.
HTH
Best,
Oliveiros
----- Original Message -----From: Thomas BOURIMECHSent: Wednesday, April 21, 2010 1:29 PMSubject: [SQL] LEFT OUTER JOIN issueHi 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
AND 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...
In article <987929295D1345B5BCE249F42730CB82@marktestcr.marktest.pt>, "Oliveiros" <oliveiros.cristina@marktest.pt> writes: > Hi, Thomas. > I believe it is because of your WHERE clause, which is filtering out the nulls > from hp table. > According to > WHERE > hp.poste_idposte = 275 > You only want registers that have hp.poste_idposte = 275, not the null ones. Yes, the WHERE effectively turns the outer into an inner join, thus removing rows from the right table. By moving the WHERE to the JOIN condition, you get the result you expected.
<font face="Calibri" size="2">Hi,</font><br /><font face="Calibri" size="2">> SELECT ct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.tFROM calendar_temp as ct</font><br /><font face="Calibri" size="2">> LEFTOUTER JOIN h_part as hp</font><br /><font face="Calibri" size="2">> ON ct.dat = hp.datmesure</font><br /><font face="Calibri"size="2">> AND ct.heur = hp.heuremesure</font><br /><font face="Calibri" size="2">> WHERE</font><br /><fontface="Calibri" size="2">> hp.poste_idposte = 275</font><br /><font face="Calibri" size="2">> ORDER BY ct.dat,ct.heur</font><br /><font face="Calibri" size="2"> </font><br /><font face="Calibri" size="2">> dat heur datmesure heuremesure t</font><br /><font face="Calibri" size="2">> -----------------------------------------------------------</font><br/><font face="Calibri" size="2">> 15/03/2008 0:00 15/03/2008 0:00 8,3</font><br /><font face="Calibri" size="2">> 15/03/2008 3:00 15/03/2008 3:00 12</font><br /><font face="Calibri" size="2">>15/03/2008 6:00 15/03/2008 6:00 15</font><br /><font face="Calibri"size="2">> 15/03/2008 9:00 15/03/2008 9:00 18</font><br /><fontface="Calibri" size="2">> 15/03/2008 12:00 null null null</font><br/><font face="Calibri" size="2">> 15/03/2008 15:00 null null null</font><br /><br /><font face="Calibri" size="2">Would this work?</font><br /><font face="Calibri" size="2">SELECTct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.t FROM calendar_temp as ct</font><br /><font face="Calibri"size="2">LEFT OUTER JOIN h_part as hp</font><br /><font face="Calibri" size="2">ON ct.dat = hp.datmesure</font><br/><font face="Calibri" size="2">AND ct.heur = hp.heuremesure</font><br /><font face="Calibri" size="2">WHERE</font><br/><font face="Calibri" size="2">coalesce(hp.poste_idposte,275) = 275</font><br /><font face="Calibri"size="2">ORDER BY ct.dat, ct.heur</font><br /><br /><font face="Calibri" size="2"> dat | heur |datmesure | heuremesure | t</font><br /><font face="Calibri" size="2">------------+----------+------------+-------------+------</font><br/><font face="Calibri" size="2"> 2008-03-15 |00:00:00 | 2008-03-15 | 00:00:00 | 8.3</font><br /><font face="Calibri" size="2"> 2008-03-15 | 03:00:00 | 2008-03-15| 03:00:00 | 12.0</font><br /><font face="Calibri" size="2"> 2008-03-15 | 06:00:00 | 2008-03-15 | 06:00:00 | 15.0</font><br /><font face="Calibri" size="2"> 2008-03-15 | 09:00:00 | 2008-03-15 | 09:00:00 | 18.0</font><br /><fontface="Calibri" size="2"> 2008-03-15 | 12:00:00 | | |</font><br /><font face="Calibri" size="2"> 2008-03-15| 15:00:00 | | |</font><br /><font face="Calibri" size="2">(6 rows)</font><br/><br /><font face="Calibri" size="2">Regards,</font><br /><font face="Calibri" size="2">Jayadevan</font><fontface="sans-serif" size="2"><br /></font><br /><font face="sans-serif" size="2"><br /><br /><br/><br /> DISCLAIMER:</font><font size="3"> </font><font color="#a2a2a2" face="Tahoma" size="1"><br /><br /> "The informationin this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidentialand/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroyall copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy,adequacy or completeness of the information contained in this email or any attachment and is not liable for anyerrors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."</font><font size="3"><br/></font><font size="1"><br /></font><font size="3"><br /></font><br /><br />
Thankx everybody
It worked...
-----Message d'origine-----
De : Oliveiros [mailto:oliveiros.cristina@marktest.pt]
Envoyé : mercredi 21 avril 2010 15:42
À : Thomas BOURIMECH; pgsql-sql@postgresql.org; Oliveiros
Objet : Re: [SQL] LEFT OUTER JOIN issue
Try moving the hp.poste_idposte=275 inside the LEFT JOIN condition, somethin like :
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
AND hp.poste_idposte = 275)
ORDER BY ct.dat, ct.heur
And drop the WHERE clause.
See if it gives the results you intended.
Best,
Oliveiros
----- Original Message ----- From: Oliveiros <mailto:oliveiros.cristina@marktest.pt> To: Thomas BOURIMECH
<mailto:thomas.bourimech@metnext.com> ; pgsql-sql@postgresql.org Sent: Wednesday, April 21, 2010 1:53 PMSubject: Re:
[SQL]LEFT OUTER JOIN issue
Hi, Thomas.
I believe it is because of your WHERE clause, which is filtering out the nulls from hp table.
According to
WHERE
hp.poste_idposte = 275
You only want registers that have hp.poste_idposte = 275, not the null ones.
HTH
Best,
Oliveiros
----- Original Message ----- From: Thomas BOURIMECH <mailto:thomas.bourimech@metnext.com> To:
'pgsql-sql@postgresql.org' Sent: Wednesday, April 21, 2010 1:29 PM Subject: [SQL] LEFT OUTER JOIN issue
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
AND 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...
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.801 / Virus Database: 271.1.1/2811 - Release Date: 04/20/10 22:14:00