Include null values in GROUP BY statement

[jav]

I have two tables: PRODUCT and CATEGORY

PRODUCT has:

product_id
name
amount
category_id

VALUES:

1, wine, 23, 2
2, rum, 12, 2
3, beer, 123, 2
4, mineral water, 210, 1

CATEGORY has:

category_id
name

VALUES:

1, non-alcoholic
2, alcoholic
3, snacks

I want to make an Access SQL query that return me:

category_id
total_amount

VALUES:

1, 210
2, 158
3, 0

I have this:

SELECT CATEGORY.category_id, SUM(PRODUCT.value)
FROM CATEGORY, PRODUCT
WHERE PRODUCT.category_id=CATEGORY.category_id

But this query doesn't return me the third category (snacks) because it
has null values. I want ir returns me the 0 value.

Thanks!!

 

[John Spencer]

Change the syntax of your query to use a LEFT JOIN

SELECT CATEGORY.category_id, SUM(PRODUCT.value)
FROM CATEGORY LEFT JOIN PRODUCT
ON PRODUCT.category_id=CATEGORY.category_id