--- On Wed, 4/21/10, Thomas BOURIMECH <thomas.bourim...@metnext.com> wrote:
From: Thomas BOURIMECH <thomas.bourim...@metnext.com>
Subject: [SQL] LEFT OUTER JOIN issue
To: "'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.org>
Date: Wednesday, April 21, 2010, 9:06 AM
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...
try..
SELECT
ct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.t
FROM
calendar_temp as ct
LEFT 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
