Re: LEFT OUTER JOIN issue

Hi, Thomas.

I believe it is because of your WHERE clause, which is filtering out the nulls from hp table.

According to

WHERE

hp.poste_idposte = 275

You only want registers that have hp.poste_idposte = 275, not the null ones.

HTH

Best,

Oliveiros

----- Original Message -----
From: Thomas BOURIMECH
To: 'pgsql-sql(at)postgresql(dot)org'
Sent: Wednesday, April 21, 2010 1:29 PM
Subject: [SQL] LEFT OUTER JOIN issue

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

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