[SOLVED] How do I count 0's in a group by query

[colinsp]

Hi

 

I am fairly new to php and mySQL I have the following query

 

SELECT
`type`.id,
`type`.category,
`type`.description,
Count(item.category_id)
FROM
`type`
Inner Join item ON `type`.id = item.category_id
GROUP BY
`type`.id
ORDER BY
`type`.category ASC

 

This returns the results I expect where there are items in the type table that correspond to a category_id BUT I want this query to also return 0 values of category.

 

The result that I want would be something like:

 

Bridge 1

Compact  0

DSLR 3

 

but all I am getting is

 

Bridge 1

DSLR 3

 

Any pointers most welcome. I have spent a couple of hours searching Google without success.

 

TIA

 

--

Colin

 

 

[Mchl]

Do you have a table, where all categories are stored?

[colinsp]

Hi

 

Yes the type table stores the category and its description

the item table stores the items and is joined by item.category_id to type.id

[Mchl]

Ok then

Try changing INNER JOIN to LEFT JOIN

[colinsp]

Brilliant. Thanks that works fine 

 

I must do some more reading about joins.