Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 63E67632FED for ; Wed, 21 Apr 2010 06:10:19 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 75692-06 for ; Wed, 21 Apr 2010 09:10:08 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from fw1.nexto.fr (smtp.nexto.fr [85.31.207.125]) by mail.postgresql.org (Postfix) with ESMTP id 10F54632E87 for ; Wed, 21 Apr 2010 06:10:07 -0300 (ADT) Received: (from uucp@localhost) by fw1.nexto.fr id o3L96WnF000322 for ; Wed, 21 Apr 2010 11:06:32 +0200 Received: from A by B ; Wed, 21 Apr 2010 09:06:27 +0000 Received: from A by B ; Wed, 21 Apr 2010 11:06:26 +0200 From: Thomas BOURIMECH To: "'pgsql-sql@postgresql.org'" Date: Wed, 21 Apr 2010 11:06:25 +0200 Subject: LEFT OUTER JOIN issue Thread-Topic: LEFT OUTER JOIN issue Thread-Index: AcrhMes8Zw+CkA3IRoGBqi+ilXJ8ZA== Message-ID: <5AF59BAE41BBA14090A31CE077580C53238EAB6FD5@EXCHANGE2007.netcenter.local> Accept-Language: fr-FR Content-Language: fr-FR X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: fr-FR Content-Type: multipart/alternative; boundary="_000_5AF59BAE41BBA14090A31CE077580C53238EAB6FD5EXCHANGE2007n_" MIME-Version: 1.0 X-F360-PVer: 0000001 X-F360-Spam: Unknown X-F360-RefID: str=0001.0A0B0209.4BCEC013.00E8,ss=1,fgs=0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.801 tagged_above=-10 required=5 tests=BAYES_50=0.8, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201004/86 X-Sequence-Number: 34013 --_000_5AF59BAE41BBA14090A31CE077580C53238EAB6FD5EXCHANGE2007n_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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", "pos= te_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 a= s ct LEFT OUTER JOIN h_part as hp ON ct.dat =3D hp.datmesure ABD ct.heur =3D hp.heuremesure WHERE hp.poste_idposte =3D 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... --_000_5AF59BAE41BBA14090A31CE077580C53238EAB6FD5EXCHANGE2007n_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi  everyone, here is my probl= em :

 

I got two tables :

 

CREATE TABLE "public"."calendar_temp" (

  "id" SERIAL, =

  "dat" DATE, <= /o:p>

  "heur" TIME WITHOU= T TIME ZONE,

  CONSTRAINT "calendar_temp_pkey" PRIMARY KEY("id")

) WITHOUT OIDS;

 

 

CREATE TABLE "public".&qu= ot;h_part" (

  "idh" SERIAL,

  "poste_idposte" IN= TEGER 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&q= uot; 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 :<= /o:p>

 

calendar_temp

-----------------

id        &nbs= p;   dat            =              heur

1         = ;    15/03/2008         0:00<= /p>

2         = ;    15/03/2008         3:00<= /p>

3         = ;    15/03/2008         6:00<= /p>

4         = ;    15/03/2008         9:00<= /p>

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  &= nbsp;           &nbs= p;        15/03/2008

5002      275            =             12           3:00            = ;           15/03/2008

5003      275            =             15           6:00 &n= bsp;            = ;         15/03/2008

5004      275            =             18           9:00 &n= bsp;            = ;         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 =3D hp.datmesure

ABD ct.heur =3D hp.heuremesure

WHERE

hp.poste_idposte =3D 275=

ORDER BY ct.dat, ct.heur=

 

dat        &nb= sp;            =     heur      datmesure         heuremesure  = ; t

-----------------------------------= ------------------------

15/03/2008    &= nbsp;    0:00       15/03/2008         0:00            = ;           8,3

15/03/2008    &= nbsp;    3:00       15/03/2008         3:00            = ;           12

15/03/2008    &= nbsp;    6:00       15/03/2008         6:00            = ;           15

15/03/2008    &= nbsp;    9:00       15/03/2008         9:00            = ;           18

15/03/2008    &= nbsp;    12:00     null       = ;            &n= bsp;    null            = ;            null

15/03/2008    &= nbsp;    15:00     null            = ;            null            = ;            null

 

 

But unfortunatly all that I get is  this set :

 

dat        &nb= sp;            =     heur      datmesure         heuremesure  = ; t

------------------------------------------------------= -----

15/03/2008    &= nbsp;    0:00       15/03/2008         0:00            = ;           8,3

15/03/2008    &= nbsp;    3:00       15/03/2008         3:00            = ;           12

15/03/2008    &= nbsp;    6:00       15/03/2008         6:00            = ;           15

15/03/2008    &= nbsp;    9:00       15/03/2008         9:00            = ;           18

 

 

Getting mad with it...

 

Thanks for any help...

 

 

--_000_5AF59BAE41BBA14090A31CE077580C53238EAB6FD5EXCHANGE2007n_--