Sandro Dentella wrote:
I've a very simple task. I thought I knew how to solve it but there's
something I'm surely missing.

I got film and scores for the film. In table "film" and "punteggio"
(score). I want a join returning all the films and the votes
expressed by the user(s). If the user did not vote I want a NULL.

I only get films for which a vote was expressed. My query:

SELECT f.id, f.titolo, p.voto
FRM film_film f
LEFT UTER JIN vota_punteggio p
N (f.id = p.film_id)
WHERE
(p.user_id = 2 R p.user_id IS NULL)

Can you help me understanding what is wrong?
TIA
sandro
*:-)

Looks to me like it is because you're referencing vota_punteggio in the
WHERE clause.

Try something like this:

SELECT f.id, f.titolo, p.voto
FRM film_film f
LEFT UTER JIN vota_punteggio p
N f.id = p.film_id
AND p.user_id = 2

cinemino=# \d film_film
Tabella "public.film_film"
Colonna | Tipo | Modificatori
++
id | integer | not null default nextval('film_film_id_seq'::regclass)
titolo | character varying(100) | not null
regista | character varying(40) |
url_iann | character varying(200) |
url_altri | character varying(200) |
anno | integer |
image | character varying(100) |
durata | integer |
genere_id | integer |
data_proiezione | date |
proposto_da | integer |

cinemino=# \d vota_punteggio
Tabella "public.vota_punteggio"
Colonna | Tipo | Modificatori
++
id | integer | not null default nextval('vota_punteggio_id_seq'::regclass)
voto | integer | not null
user_id | integer | not null
film_id | integer | not null

(end of broadcast)
TIP 6: explain analyze is your friend

Sandro Dentella <sandro (AT) e-den (DOT) itwrites:
I only get films for which a vote was expressed. My query:

SELECT f.id, f.titolo, p.voto
FRM film_film f
LEFT UTER JIN vota_punteggio p
N (f.id = p.film_id)
WHERE
(p.user_id = 2 R p.user_id IS NULL)

Can you help me understanding what is wrong?

[ scratches head ] Looks all right to me. Are you sure you copied
what you typed accurately? Does EXPLAIN show that a left join is being
used?

regards, tom lane

(end of broadcast)
TIP 3: Have you checked our extensive FAQ?

Sandro Dentella <sandro (AT) e-den (DOT) itwrites:
this works in fact, and it's simpler. But I don't really understard why I
should put it in this way.

The other way seems to work for me:

regression=# create table film_film (id int, titolo text);
CREATE TABLE
regression=# create table vota_punteggio(film_id int, user_id int, voto int);
CREATE TABLE
regression=# insert into film_film values(1, 'one');
INSERT 0 1
regression=# insert into film_film values(2, 'two');
INSERT 0 1
regression=# insert into vota_punteggio values(1, 2, 10);
INSERT 0 1
regression=# SELECT f.id, f.titolo, p.voto
regression-# FRM film_film f LEFT UTER JIN vota_punteggio p
regression-# N (f.id = p.film_id)
regression-# WHERE (p.user_id = 2 R p.user_id IS NULL);
id | titolo | voto
++
1 | one | 10
2 | two |
(2 rows)

Can you put together a self-contained test case showing the problem?
What PG version are you running, anyway? (There's a known bug in merge
right join in 8.1.0-8.1.3, but AFAIR the symptom is too many output rows
not too few.)

regards, tom lane

(end of broadcast)
TIP 5: don't forget to increase your free space map settings

Sandro Dentella wrote:
Fri, 27, 2006 at 02:42:06PM -0500, Russ Brown wrote:
>Looks to me like it is because you're referencing vota_punteggio in the
>WHERE clause.
>>
>Try something like this:
>>
>SELECT f.id, f.titolo, p.voto
>FRM film_film f
>LEFT UTER JIN vota_punteggio p
>N f.id = p.film_id
>AND p.user_id = 2
>>

My thinking was that by putting the conditions in the WHERE clause you
were restricting the rows returned by the entire query, while putting
them in the N clause only restricts the matching in the JIN itself.

this works in fact, and it's simpler. But I don't really understard why I
should put it in this way.

Fri, 27, 2006 at 03:55:35PM -0400, Tom Lane wrote:
>[ scratches head ] Looks all right to me. Are you sure you copied
>what you typed accurately? Does EXPLAIN show that a left join is being
>used?

here is the explain for both queries:

cinemino=# explain SELECT f.id, f.titolo, p.voto
FRM film_film f
LEFT UTER JIN vota_punteggio p
N (f.id = p.film_id)
WHERE
(p.user_id = 2 R p.user_id IS NULL)
;
QUERY PLAN

Merge Right Join (cost=18.00106.26 rows=170 width=76)
Merge Cond: ("outer".film_id = "inner".id)
Filter: (("outer".user_id = 2) R ("outer".user_id IS NULL))
-Index Scan using vota_punteggio_film_id on vota_punteggio p (cost=0.0059.93 rows=1630 width=12)
-Sort (cost=18.0018.42 rows=170 width=72)
Sort Key: f.id
-Seq Scan on film_film f (cost=0.0011.70 rows=170 width=72)
(7 righe)

cinemino=# explain SELECT f.id, f.titolo, p.voto
FRM film_film f
LEFT UTER JIN vota_punteggio p
N (f.id = p.film_id AND p.user_id = 2)
;
QUERY PLAN

Hash Left Join (cost=12.0524.67 rows=170 width=76)
Hash Cond: ("outer".id = "inner".film_id)
-Seq Scan on film_film f (cost=0.0011.70 rows=170 width=72)
-Hash (cost=12.0312.03 rows=8 width=8)
-Bitmap Heap Scan on vota_punteggio p (cost=2.0312.03 rows=8 width=8)
Recheck Cond: (user_id = 2)
-Bitmap Index Scan on vota_punteggio_user_id (cost=0.002.03 rows=8 width=0)
Index Cond: (user_id = 2)
(8 righe)

BTW: I'm no able to read explain output, but it's a long time I want to
start studying them. I think I should start studying chapter 13, other
hints on this subject?

I really need to read up more on that myself. Looking at the above I
really couldn't tell you which is the 'better' query plan.

(end of broadcast)
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly