Re: LEFT OUTER JOIN issue

--- On Wed, 4/21/10, Thomas BOURIMECH <thomas(dot)bourimech(at)metnext(dot)com> wrote:

From: Thomas BOURIMECH <thomas(dot)bourimech(at)metnext(dot)com>
Subject: [SQL] LEFT OUTER JOIN issue
To: "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org>
Date: Wednesday, April 21, 2010, 9:06 AM

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

 

try..
SELECT
     ct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.t
FROM
   calendar_temp as ct

LEFT 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