Re: Simple OUTER JOIN doubt

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
> 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
> *:-)
>

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
FROM film_film f
LEFT OUTER JOIN vota_punteggio p
ON 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
>
>
>