LEFT OUTER JOIN issue

From: Thomas BOURIMECH <thomas(dot)bourimech(at)metnext(dot)com>
To: "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org>
Subject: LEFT OUTER JOIN issue
Date: 2010-04-21 09:06:25
Message-ID: 5AF59BAE41BBA14090A31CE077580C53238EAB6FD5@EXCHANGE2007.netcenter.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Thomas BOURIMECH 2010-04-21 12:29:56 LEFT OUTER JOIN issue
Previous Message silly sad 2010-04-21 07:43:06 Re: How to max() make null as biggest value?