pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLEFT OUTER JOIN issue
8+ messages / 6 participants
[nested] [flat]
* LEFT OUTER JOIN issue
@ 2010-04-21 09:06 Thomas BOURIMECH <thomas.bourimech@metnext.com>
2010-04-22 19:14 ` Re: LEFT OUTER JOIN issue Lennin Caro <lennin.caro@yahoo.com>
2010-04-22 23:02 ` Re: LEFT OUTER JOIN issue Arne Stölck <stolck@web.de>
0 siblings, 2 replies; 8+ messages in thread
From: Thomas BOURIMECH @ 2010-04-21 09:06 UTC (permalink / raw)
To: pgsql-sql
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...
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: LEFT OUTER JOIN issue
2010-04-21 09:06 LEFT OUTER JOIN issue Thomas BOURIMECH <thomas.bourimech@metnext.com>
@ 2010-04-22 19:14 ` Lennin Caro <lennin.caro@yahoo.com>
1 sibling, 0 replies; 8+ messages in thread
From: Lennin Caro @ 2010-04-22 19:14 UTC (permalink / raw)
To: pgsql-sql; Thomas BOURIMECH <thomas.bourimech@metnext.com>
--- On Wed, 4/21/10, Thomas BOURIMECH <thomas.bourimech@metnext.com> wrote:
From: Thomas BOURIMECH <thomas.bourimech@metnext.com>
Subject: [SQL] LEFT OUTER JOIN issue
To: "'pgsql-sql@postgresql.org'" <pgsql-sql@postgresql.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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: LEFT OUTER JOIN issue
2010-04-21 09:06 LEFT OUTER JOIN issue Thomas BOURIMECH <thomas.bourimech@metnext.com>
@ 2010-04-22 23:02 ` Arne Stölck <stolck@web.de>
1 sibling, 0 replies; 8+ messages in thread
From: Arne Stölck @ 2010-04-22 23:02 UTC (permalink / raw)
To: pgsql-sql
Le Wed, 21 Apr 2010 11:06:25 +0200,
Thomas BOURIMECH <thomas.bourimech@metnext.com> a écrit :
> 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...
you have to consider the where condition. In those lines where you
expect null values, he poste_idposte field would be null as well.
for example, the following query returns the expected result :
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
coalesce(hp.poste_idposte,275) = 275
ORDER BY ct.dat, ct.heur;
^ permalink raw reply [nested|flat] 8+ messages in thread
* LEFT OUTER JOIN issue
@ 2010-04-21 12:29 Thomas BOURIMECH <thomas.bourimech@metnext.com>
2010-04-21 12:53 ` Re: LEFT OUTER JOIN issue Oliveiros <oliveiros.cristina@marktest.pt>
2010-04-22 03:41 ` Re: LEFT OUTER JOIN issue Jayadevan M <Jayadevan.Maymala@ibsplc.com>
0 siblings, 2 replies; 8+ messages in thread
From: Thomas BOURIMECH @ 2010-04-21 12:29 UTC (permalink / raw)
To: pgsql-sql
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...
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: LEFT OUTER JOIN issue
2010-04-21 12:29 LEFT OUTER JOIN issue Thomas BOURIMECH <thomas.bourimech@metnext.com>
@ 2010-04-21 12:53 ` Oliveiros <oliveiros.cristina@marktest.pt>
2010-04-21 16:33 ` Re: LEFT OUTER JOIN issue Harald Fuchs <hari.fuchs@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Oliveiros @ 2010-04-21 12:53 UTC (permalink / raw)
To: Thomas BOURIMECH <thomas.bourimech@metnext.com>; pgsql-sql
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...
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: LEFT OUTER JOIN issue
2010-04-21 12:29 LEFT OUTER JOIN issue Thomas BOURIMECH <thomas.bourimech@metnext.com>
2010-04-21 12:53 ` Re: LEFT OUTER JOIN issue Oliveiros <oliveiros.cristina@marktest.pt>
@ 2010-04-21 16:33 ` Harald Fuchs <hari.fuchs@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Harald Fuchs @ 2010-04-21 16:33 UTC (permalink / raw)
To: pgsql-sql
In article <987929295D1345B5BCE249F42730CB82@marktestcr.marktest.pt>,
"Oliveiros" <oliveiros.cristina@marktest.pt> writes:
> 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.
Yes, the WHERE effectively turns the outer into an inner join, thus
removing rows from the right table. By moving the WHERE to the JOIN
condition, you get the result you expected.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: LEFT OUTER JOIN issue
2010-04-21 12:29 LEFT OUTER JOIN issue Thomas BOURIMECH <thomas.bourimech@metnext.com>
@ 2010-04-22 03:41 ` Jayadevan M <Jayadevan.Maymala@ibsplc.com>
1 sibling, 0 replies; 8+ messages in thread
From: Jayadevan M @ 2010-04-22 03:41 UTC (permalink / raw)
To: Thomas BOURIMECH <thomas.bourimech@metnext.com>; +Cc: pgsql-sql; pgsql-sql-owner@postgresql.org
Hi,
> 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
Would this work?
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
coalesce(hp.poste_idposte,275) = 275
ORDER BY ct.dat, ct.heur
dat | heur | datmesure | heuremesure | t
------------+----------+------------+-------------+------
2008-03-15 | 00:00:00 | 2008-03-15 | 00:00:00 | 8.3
2008-03-15 | 03:00:00 | 2008-03-15 | 03:00:00 | 12.0
2008-03-15 | 06:00:00 | 2008-03-15 | 06:00:00 | 15.0
2008-03-15 | 09:00:00 | 2008-03-15 | 09:00:00 | 18.0
2008-03-15 | 12:00:00 | | |
2008-03-15 | 15:00:00 | | |
(6 rows)
Regards,
Jayadevan
DISCLAIMER:
"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: LEFT OUTER JOIN issue
@ 2010-04-21 13:42 Oliveiros <oliveiros.cristina@marktest.pt>
0 siblings, 0 replies; 8+ messages in thread
From: Oliveiros @ 2010-04-21 13:42 UTC (permalink / raw)
To: Thomas BOURIMECH <thomas.bourimech@metnext.com>; pgsql-sql; Oliveiros <oliveiros.cristina@gmail.com>
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
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...
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2010-04-22 23:02 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-21 09:06 LEFT OUTER JOIN issue Thomas BOURIMECH <thomas.bourimech@metnext.com>
2010-04-22 19:14 ` Lennin Caro <lennin.caro@yahoo.com>
2010-04-22 23:02 ` Arne Stölck <stolck@web.de>
2010-04-21 12:29 LEFT OUTER JOIN issue Thomas BOURIMECH <thomas.bourimech@metnext.com>
2010-04-21 12:53 ` Oliveiros <oliveiros.cristina@marktest.pt>
2010-04-21 16:33 ` Harald Fuchs <hari.fuchs@gmail.com>
2010-04-22 03:41 ` Jayadevan M <Jayadevan.Maymala@ibsplc.com>
2010-04-21 13:42 Re: LEFT OUTER JOIN issue Oliveiros <oliveiros.cristina@marktest.pt>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox