Search Postgresql Archives

Re: Simple OUTER JOIN doubt

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux