Thankx everybody
It worked...
-----Message d'origine-----
De : Oliveiros [mailto:oliveiros.crist...@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.crist...@marktest.pt>
To: Thomas BOURIMECH <mailto:thomas.bourim...@metnext.com> ;
pgsql-sql@postgresql.org
Sent: Wednesday, April 21, 2010 1:53 PM
Subject: 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.bourim...@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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
