left join and where

From: aannddrree(at)libero(dot)it (andrea)
To: pgsql-sql(at)postgresql(dot)org
Subject: left join and where
Date: 2001-12-27 20:28:43
Message-ID: c837c0fc.0112271228.1ce47b3a@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Roland Roberts 2001-12-27 21:14:23 Working with array value columns in PL/pgSQL triggers/functions
Previous Message Richard Rowell 2001-12-27 05:36:05 Multiple counts