sql count() in a different table

[Andreas Boehmer]

This is what I want to do in Access:

I have got a table with categories. They have got a name and a categoryID:
categories (categoryID, name)

A related table uses the categoryID as a foreign key:
companyCategories (categoryID, companyID)

What I would like to do is display all the names of the categories, as well
as the amount of times the categoryID appears in the related table
(companyCategories).

So for example as a result of the search:

Hats, 4
Shoes, 5
Pencils, 23

The category Hats appears 4 times in the table companyCategories. Shoes
appears 5 times,...

I can't figure out how to write the SQL statement that joins both. Any
ideas?

 

[Stephan Oetzel, ITSM GmbH]

Hi,

SELECT ca.category_name, count(co.category_id) FROM categories ca, comanies
co WHERE ca.category_id = co.category_id GROUP BY ca.category_name

Should be something like this...

 

[Andreas Boehmer]

Hi,

SELECT ca.category_name, count(co.category_id) FROM categories ca, comanies
co WHERE ca.category_id = co.category_id GROUP BY ca.category_name

Should be something like this...

Thanks for your help. This is almost right. The problem is that it only
displays those category names, that are in companyCategory. But I also want
to show the names, that have got no entries in companyCategory.

Here another example of the results I wish:

Hats, 4
Shoes, 5
Toiletry, 0
Pencils, 23

In the sql statement above, the toiletry does not show up. I know it's
because of the "ca.category_id = co.category_id ", but I don't know how to
fix it.

Sorry, my mind seems blocked today!

 

[Douglas J. Steele]

Try:

SELECT ca.category_name, count(co.category_id)
FROM categories ca
LEFT JOIN companies co
ON ca.category_id = co.category_id
GROUP BY ca.category_name

 

[Andreas Boehmer]

Thank you. Excellent!

Try:

SELECT ca.category_name, count(co.category_id)
FROM categories ca
LEFT JOIN companies co
ON ca.category_id = co.category_id
GROUP BY ca.category_name