Re: LEFT OUTER JOIN issue

Le Wed, 21 Apr 2010 11:06:25 +0200,
Thomas BOURIMECH <thomas(dot)bourimech(at)metnext(dot)com> a écrit :

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

you have to consider the where condition. In those lines where you
expect null values, he poste_idposte field would be null as well.

for example, the following query returns the expected result :

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
coalesce(hp.poste_idposte,275) = 275
ORDER BY ct.dat, ct.heur;