Re: LEFT OUTER JOIN issue - Mailing list pgsql-sql

Le Wed, 21 Apr 2010 11:06:25 +0200,
Thomas BOURIMECH <thomas.bourimech@metnext.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;