From: Lennin Caro <lennin.caro@yahoo.com>
To: 'pgsql-sql@postgresql.org' <pgsql-sql@postgresql.org>
To: Thomas BOURIMECH <thomas.bourimech@metnext.com>
Subject: Re: LEFT OUTER JOIN issue
Date: Thu, 22 Apr 2010 12:14:50 -0700 (PDT)
Message-ID: <815510.79882.qm@web59516.mail.ac4.yahoo.com> (raw)
In-Reply-To: <5AF59BAE41BBA14090A31CE077580C53238EAB6FD5@EXCHANGE2007.netcenter.local>
--- On Wed, 4/21/10, Thomas BOURIMECH <thomas.bourimech@metnext.com> wrote:
From: Thomas BOURIMECH <thomas.bourimech@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lennin.caro@yahoo.com, thomas.bourimech@metnext.com
Subject: Re: LEFT OUTER JOIN issue
In-Reply-To: <815510.79882.qm@web59516.mail.ac4.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox