Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id E51C0633753 for ; Wed, 21 Apr 2010 10:42:34 -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 22041-01 for ; Wed, 21 Apr 2010 13:42:24 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116]) by mail.postgresql.org (Postfix) with ESMTP id 0AD5863332E for ; Wed, 21 Apr 2010 10:42:07 -0300 (ADT) Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id GK8F2M72; Wed, 21 Apr 2010 14:42:04 +0100 Message-ID: <8111AC40AA9840A489285D145B52A2BB@marktestcr.marktest.pt> From: "Oliveiros" To: "Thomas BOURIMECH" , , "Oliveiros" Subject: Re: LEFT OUTER JOIN issue Date: Wed, 21 Apr 2010 14:42:03 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00E2_01CAE160.CEDBECF0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5512 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.277 tagged_above=-10 required=5 tests=BAYES_50=0.8, FAKE_REPLY_C=1.486, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: ** X-Archive-Number: 201004/80 X-Sequence-Number: 34007 This is a multi-part message in MIME format. ------=_NextPart_000_00E2_01CAE160.CEDBECF0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Try moving the hp.poste_idposte=3D275 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 =3D hp.datmesure AND ct.heur =3D hp.heuremesure AND hp.poste_idposte =3D 275) ORDER BY ct.dat, ct.heur And drop the WHERE clause. See if it gives the results you intended. Best, Oliveiros ----- Original Message -----=20 From: Oliveiros=20 To: Thomas BOURIMECH ; pgsql-sql@postgresql.org=20 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 =3D 275 You only want registers that have hp.poste_idposte =3D 275, not the = null ones. HTH Best, Oliveiros ----- Original Message -----=20 From: Thomas BOURIMECH=20 To: 'pgsql-sql@postgresql.org'=20 Sent: Wednesday, April 21, 2010 1:29 PM Subject: [SQL] LEFT OUTER JOIN issue Hi everyone, here is my problem :=20 =20 I got two tables : =20 CREATE TABLE "public"."calendar_temp" ( "id" SERIAL,=20 "dat" DATE,=20 "heur" TIME WITHOUT TIME ZONE,=20 CONSTRAINT "calendar_temp_pkey" PRIMARY KEY("id") ) WITHOUT OIDS; =20 =20 CREATE TABLE "public"."h_part" ( "idh" SERIAL,=20 "poste_idposte" INTEGER NOT NULL,=20 "t" NUMERIC(4,1),=20 "heuremesure" TIME WITHOUT TIME ZONE,=20 "datmesure" DATE,=20 CONSTRAINT "h_part_datmesure_key" UNIQUE("datmesure", = "heuremesure", "poste_idposte"),=20 CONSTRAINT "h_part_pkey" PRIMARY KEY("idh"),=20 CONSTRAINT "h_part_fk" FOREIGN KEY ("poste_idposte") REFERENCES "public"."poste"("idposte") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE ) WITHOUT OIDS; =20 =20 Data in table are like this : =20 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 =20 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 =20 I expect the following data set as a result from the following = request :=20 =20 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 AND ct.heur =3D hp.heuremesure WHERE hp.poste_idposte =3D 275 ORDER BY ct.dat, ct.heur =20 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 =20 =20 But unfortunatly all that I get is this set :=20 =20 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 =20 =20 Getting mad with it... =20 Thanks for any help... =20 =20 =20 ------=_NextPart_000_00E2_01CAE160.CEDBECF0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Try moving the hp.poste_idposte=3D275 = inside the LEFT=20 JOIN condition, somethin like :
 

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

LEFT OUTER JOIN h_part as=20 hp

ON (ct.dat =3D hp.datmesure

AND ct.heur =3D hp.heuremesure

AND hp.poste_idposte =3D = 275)

ORDER BY ct.dat, = ct.heur

 

And drop the=20 WHERE clause.

 

See = if it gives the=20 results you intended.

 

Best,

Oliveiros

----- Original Message -----
From:=20 Oliveiros
Sent: Wednesday, April 21, 2010 = 1:53=20 PM
Subject: Re: [SQL] LEFT OUTER = JOIN=20 issue

Hi,=20 Thomas.

 

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

According=20 to

WHERE

hp.poste_idposte =3D = 275

 

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

 

HTH

 

Best,

Oliveiros

----- Original Message -----
From:=20 Thomas BOURIMECH =
Sent: Wednesday, April 21, = 2010 1:29=20 PM
Subject: [SQL] LEFT OUTER = JOIN=20 issue

Hi  everyone, here is = my problem :=20

 

I got two tables = :

 

CREATE TABLE = "public"."calendar_temp"=20 (

  "id" SERIAL,=20

  "dat" DATE,=20

  "heur" TIME WITHOUT = TIME ZONE,=20

  CONSTRAINT = "calendar_temp_pkey"=20 PRIMARY KEY("id")

) WITHOUT = OIDS;

 

 

CREATE TABLE = "public"."h_part"=20 (

  "idh" SERIAL,=20

  "poste_idposte" = INTEGER NOT NULL,=20

  "t" NUMERIC(4,1),=20

  "heuremesure" TIME = WITHOUT TIME=20 ZONE,

  "datmesure" = DATE,=20

  CONSTRAINT "h_part_datmesure_key"=20 UNIQUE("datmesure", "heuremesure", "poste_idposte"),

  CONSTRAINT = "h_part_pkey" PRIMARY=20 KEY("idh"),

  CONSTRAINT = "h_part_fk" FOREIGN=20 KEY ("poste_idposte")

  REFERENCES=20 "public"."poste"("idposte")

  ON DELETE NO=20 ACTION

  ON UPDATE NO=20 ACTION

  NOT=20 DEFERRABLE

) WITHOUT = OIDS;

 

 

Data in table are like this=20 :

 

calendar_temp

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

id         = ;  =20 = dat           &nbs= p;            = ;=20 heur

1         =    =20 15/03/2008        =20 0:00

2         =    =20 15/03/2008        =20 3:00

3         =    =20 15/03/2008        =20 6:00

4         =    =20 15/03/2008        =20 9:00

5         =    =20 15/03/2008        =20 12:00

6         =    =20 15/03/2008        =20 15:00

 

h_part

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

idh        =20 poste_idposte  t=20 =              = heuremesure   datmesure

5001     =20 = 275           &nbs= p;           =20 8,3         =20 = 0:00           &nb= sp;          =20 15/03/2008

5002     =20 = 275           &nbs= p;           =20 12          =20 = 3:00           &nb= sp;          =20 15/03/2008

5003     =20 = 275           &nbs= p;           =20 15          =20 = 6:00           &nb= sp;          =20 15/03/2008

5004     =20 = 275           &nbs= p;           =20 18          =20 = 9:00           &nb= sp;          =20 15/03/2008

 

I expect the following data = set as a=20 result from the following request :

 

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

LEFT OUTER JOIN h_part as=20 hp

ON ct.dat =3D hp.datmesure

AND ct.heur =3D hp.heuremesure

WHERE

hp.poste_idposte =3D=20 275

ORDER BY ct.dat,=20 ct.heur

 

dat        &nbs= p;            = ;   =20 heur     =20 datmesure         = heuremesure  =20 t

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

15/03/2008        =20 0:00      =20 15/03/2008        =20 = 0:00           &nb= sp;          =20 8,3

15/03/2008        =20 3:00      =20 15/03/2008        =20 = 3:00           &nb= sp;          =20 12

15/03/2008        =20 6:00      =20 15/03/2008        =20 = 6:00           &nb= sp;          =20 15

15/03/2008        =20 9:00      =20 15/03/2008        =20 = 9:00           &nb= sp;          =20 18

15/03/2008        =20 12:00    =20 = null           &nb= sp;           =20 = null           &nb= sp;           =20 null

15/03/2008        =20 15:00    =20 = null           &nb= sp;           =20 = null           &nb= sp;           =20 null

 

 

But unfortunatly all that I = get is=20  this set :

 

dat        &nbs= p;            = ;   =20 heur     =20 datmesure         = heuremesure  =20 t

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

15/03/2008        =20 0:00      =20 15/03/2008        =20 = 0:00           &nb= sp;          =20 8,3

15/03/2008        =20 3:00      =20 15/03/2008        =20 = 3:00           &nb= sp;          =20 12

15/03/2008        =20 6:00      =20 15/03/2008        =20 = 6:00           &nb= sp;          =20 15

15/03/2008        =20 9:00      =20 15/03/2008        =20 = 9:00           &nb= sp;          =20 18

 

 

Getting mad with=20 it...

 

Thanks for any=20 help...

 

 

 

= ------=_NextPart_000_00E2_01CAE160.CEDBECF0--