Re: LEFT OUTER JOIN issue [SOLVED] - Mailing list pgsql-sql

Thankx everybody
It worked...

-----Message d'origine-----
De : Oliveiros [mailto:oliveiros.cristina@marktest.pt]
Envoyé : mercredi 21 avril 2010 15:42
À : Thomas BOURIMECH; pgsql-sql@postgresql.org; Oliveiros
Objet : Re: [SQL] LEFT OUTER JOIN issue

Try moving the hp.poste_idposte=275 inside the LEFT JOIN condition, somethin like :
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

AND hp.poste_idposte = 275)

ORDER BY ct.dat, ct.heur


And drop the WHERE clause.


See if it gives the results you intended.


Best,

Oliveiros
----- Original Message ----- From: Oliveiros <mailto:oliveiros.cristina@marktest.pt>  To: Thomas BOURIMECH
<mailto:thomas.bourimech@metnext.com> ; pgsql-sql@postgresql.org Sent: Wednesday, April 21, 2010 1:53 PMSubject: 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 <mailto:thomas.bourimech@metnext.com>      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...





No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.801 / Virus Database: 271.1.1/2811 - Release Date: 04/20/10 22:14:00