sql count() in a different table

  • Thread starter Thread starter Andreas Boehmer
  • Start date Start date
A

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?
 
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...
 
Stephan Oetzel said:
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...
Click to expand...

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!
 
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
 
Thank you. Excellent!


Douglas J. Steele said:
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
Click to expand...
 
Back
Top