Hi There,
I've been battling with some SQL and can't seem to get my head around it.
I have two tables, one with the list of categories and another with all my articles.
What i'm trying to do is find how many articles are present for each category.
Here is the SQL I have so far
SELECT DISTINCT COUNT( po.post_Cat_ID ) AS Occurances, ca.cat_Title
FROM Posts po, Categories ca
WHERE ca.cat_ID = LEFT( po.post_Cat_ID, 2 )
The reason I use LEFT is to only get the main categories as I have listed categories as the following... for example
Science = 01
Medicine = 0101
Sport = 02
Posts on say asprin would therefore have a cat_ID as 0101. (LEFT would then trim 0101, 0102, 0103 etc to just 01). Basically im not interested in the subcategories.
Thanks in advance
Result
SELECT DISTINCT COUNT( po.post_Cat_ID ) AS Occurances, ca.cat_Title
FROM Posts po, Categories ca
WHERE ca.cat_ID = LEFT( po.post_Cat_ID, 2 )
GROUP BY LEFT( po.post_Cat_ID, 2 )
p.s. thanks @nullpointer, it works for the moment, i'll look into restructuring for other readers heres the link again
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html