left join and where - Mailing list pgsql-sql

From aannddrree@libero.it (andrea)
Subject left join and where
Date
Msg-id c837c0fc.0112271228.1ce47b3a@posting.google.com
Whole thread Raw
Responses Re: left join and where
List pgsql-sql
hello,
I'm a beginner and I have a trouble with SQL and Access 97
I try to explain the problem:

I have a table colori:

ID  colore
1   red
2   blu
3   green

and a table Vendite

ID colore anno quantita
1   red    1     10
2   blu    1     20
3   green  2     30

I want a query that return all "quantita" but only for a year (for example
if year =1, I don't extarct green)
The result must be:

colore anno quantita
red     1     10
blu     1     20          
green       
------------------------------------

I use this SQL code

SELECT colori.colore, vendite.anno, sum(vendite.quantita)
FROM colori
LEFT JOIN vendite ON colori.colore=vendite.colore
WHERE vendite.anno=1 OR vendite.anno Is Null
GROUP BY colori.colore, vendite.anno

But the result is

colore anno quantita
red     1     10
blu     1     20          

How can I get the correct result

Thank's for the help

Andrea


pgsql-sql by date:

Previous
From: Roland Roberts
Date:
Subject: Working with array value columns in PL/pgSQL triggers/functions
Next
From: "Karl Raven"
Date:
Subject: change null to 0 in SQL script