date:20100422

Re: [SQL] LEFT OUTER JOIN issue [SOLVED]

2010-04-22 Thread Thomas BOURIMECH

Thankx everybody
It worked...

-Message d'origine-
De : Oliveiros [mailto:oliveiros.crist...@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   
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

-

iddat 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  2758,3  0:00  
 15/03/2008

5002  27512   3:00  
 15/03/2008

5003  27515   6:00  
 15/03/2008

5004  27518   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

[SQL] LEFT OUTER JOIN issue

2010-04-22 Thread Thomas BOURIMECH

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
-
iddat 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  2758,3  0:00   
15/03/2008
5002  27512   3:00   
15/03/2008
5003  27515   6:00   
15/03/2008
5004  27518   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 nullnull   
 null
15/03/2008 15:00 nullnull   
 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...

Re: [SQL] LEFT OUTER JOIN issue

2010-04-22 Thread Lennin Caro

--- On Wed, 4/21/10, Thomas BOURIMECH  wrote:

From: Thomas BOURIMECH 
Subject: [SQL] LEFT OUTER JOIN issue
To: "'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

Re: [SQL] LEFT OUTER JOIN issue

2010-04-22 Thread Arne Stölck

Le Wed, 21 Apr 2010 11:06:25 +0200,
Thomas BOURIMECH  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
> -
> iddat 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  2758,3
> 0:00   15/03/2008 5002
> 27512   3:00
> 15/03/2008 5003  27515
> 6:00   15/03/2008 5004
> 27518   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
> nullnullnull
> 15/03/2008 15:00 null
> nullnull
> 
> 
> 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;

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Inconsistent Nested Set Moves

2010-04-22 Thread Amiri Barksdale

Hi folks:

I am working on a nested set implementation using some of theadvice I
found in the archives, namely in this thread:

http://archives.postgresql.org/pgsql-sql/2002-11/msg00358.php

However, I don't seem to be able to get consistent results. More than
half the time I get a duplicate primary key error. Sometimes the update
goes through though.

Here is my schema:

   CREATE TABLE nested_set (
   id integer NOT NULL,
   lft integer NOT NULL,
   rgt integer NOT NULL,
   title character varying,
   text text
   );
   ALTER TABLE ONLY nested_set
   ADD CONSTRAINT nested_set_pkey PRIMARY KEY (lft, rgt);


And here is my sproc:

   CREATE FUNCTION pg_move_tree(integer, integer) RETURNS integer
   LANGUAGE plpgsql
   AS $_$

   -- Moves part of a nested set tree to another part.
   -- Pass in the left of the child (from) and the left of the parent (to)

   DECLARE

   cleft INTEGER; cright INTEGER;
   pleft INTEGER; pright INTEGER;
   leftbound INTEGER; rightbound INTEGER;
   treeshift INTEGER; cwidth INTEGER;
   lrange INTEGER; rrange INTEGER;

   BEGIN

   SELECT lft, rgt FROM nested_set WHERE lft = $1 INTO cleft, cright;
   SELECT lft, rgt FROM nested_set WHERE lft = $2 INTO pleft, pright;

   -- Make sure the child exists
   IF cleft IS NULL THEN
   RETURN 0;
   END IF;

   -- Make sure the parent exists
   IF pleft IS NULL THEN
   RETURN 0;
   END IF;

   -- Self-move makes no sense
   IF cleft = pleft THEN
   RETURN 0;
   END IF;

   -- Parent cannot be underneath the child
   IF pleft BETWEEN cleft AND cright THEN
   RETURN 0;
   END IF;

   -- Child may already be in the proper place
   IF cleft = pleft+1 THEN
   RETURN 1;
   END IF;

   IF cleft > pleft THEN 
   treeshift  := pleft - cleft + 1;
   leftbound  := pleft+1;
   rightbound := cleft-1;
   cwidth := cright-cleft+1;
   lrange  :=  cright;
   rrange :=  pleft;
   ELSE
   treeshift  := pleft - cright;
   leftbound  := cright + 1;
   rightbound := pleft;
   cwidth := cleft-cright-1;
   lrange  :=  pleft + 1;
   rrange :=  cleft;
   END IF;


   UPDATE nested_set
   SET lft = CASE
   WHEN lft BETWEEN leftbound AND rightbound THEN lft + cwidth
   WHEN lft BETWEEN cleft AND cright THEN lft + treeshift
   ELSE lft END,
   rgt = CASE
   WHEN rgt BETWEEN leftbound AND rightbound THEN rgt + cwidth
   WHEN rgt BETWEEN cleft AND cright THEN rgt + treeshift
   ELSE rgt END;
   --WHERE lft < lrange OR rgt > rrange;
   RETURN 1;

   END;
   $_$;

Can someone help me debug this? It seems all fine. I realize this recipe
is 8 years old, but it should still work, no?

Amiri

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] LEFT OUTER JOIN issue [SOLVED]

[SQL] LEFT OUTER JOIN issue

Re: [SQL] LEFT OUTER JOIN issue

Re: [SQL] LEFT OUTER JOIN issue

[SQL] Inconsistent Nested Set Moves

5 matches

Site Navigation

Mail list logo

Footer information