Topic: using a count when using 2 tables

[Box]

Not really sure on how to do this as i'm not too great with mysql.

I have 2 tables categories and news.
news has a unique id and a category id
categories has the catagory id and the name to be used on it.

Im trying to make some stats that lists all the category names and the number of times it has been used in the news table

This is my latest attempt at it.

Code: [Select]

SELECT COUNT( * ) AS rows, news.category, categories.catname FROM news, categories GROUP BY category ORDER BY category WHERE catid=category

[Dennis1986]

SELECT c.*, COUNT(n.category) AS newsCount FROM categories AS c LEFT JOIN news AS n ON (n.category = c.id) ORDER BY c.catname ASC

Something like that.
It's a bit confusing to create it for you since I don't know the fields and they seem quite similar to eachother

[Box]

fantastic!

I've managed to get it right thanks to your code which wasnt too far off what I needed. just had to add in the group by and change the id to catid

the working code being:

Code: [Select]

SELECT c.*, COUNT(n.category) AS newsCount FROM categories AS c LEFT JOIN news AS n ON (n.category = c.catid) GROUP BY c.catname ORDER BY c.catname ASC