Simple OUTER JOIN doubt

From: Sandro Dentella <sandro(at)e-den(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: Simple OUTER JOIN doubt
Date: 2006-10-27 18:48:30
Message-ID: 20061027184830.GA21813@casa.e-den.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
FROM film_film f
LEFT OUTER JOIN vota_punteggio p
ON (f.id = p.film_id)
WHERE
(p.user_id = 2 OR p.user_id IS NULL)

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

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

--
Sandro Dentella *:-)
e-mail: sandro(at)e-den(dot)it
http://www.tksql.org TkSQL Home page - My GPL work

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Maurice Yarrow 2006-10-27 18:50:42 CREATE TABLE initial value for PRIMARY KEY
Previous Message David Fetter 2006-10-27 18:27:06 Re: DBI-Link, Oracle, database encoding