Re: LEFT OUTER JOIN issue

----- Original Message -----

From: Oliveiros

To: Thomas BOURIMECH ; pgsql-sql@postgresql.org

Sent: Wednesday, April 21, 2010 1:53 PM

Subject: Re: [SQL] 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@postgresql.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...