JOIN count wont show items with 0 results

Hey all,

I've been playing around with this query to get a count of then number of times a particular tag occurs in a relational table.

I have 2 tables

TAGS
id, tag
1, tag1
2, tag2
3, tag3
4, tag4

COMMENT_TAGS
comment_id, tag_id
1, 1
2, 1
3, 2

Here is the I've come up with

SELECT * FROM (SELECT T.tag, COUNT(C.tag_id) AS num FROM tags T JOIN comment_tags C ON T.id = C.tag_id GROUP BY T.tag) AS A WHERE A.tag LIKE '%tag%' LIMIT 10

I get the following

tag, num
tag1, 2
tag2, 1

What I'm trying to get it

tag, num
tag1, 2
tag2, 1
tag3, 0
tag4, 0

Am I trying to do this the wrong way?

Any help would be appreciated.

That is exactly correct. A JOIN across two tables requires records in both tables; if one table does not have matching rows (according to whatever condition you used) then the corresponding rows from the original table will not appear either. Which sounds right in my head.

You can use outer joins: LEFT [OUTER] and RIGHT [OUTER] JOIN. Values from rows that don't exist will be NULL. COUNT will then not count those rows.
Without double-checking my quick copy/paste,

Besides the added "LEFT" it's the same query as before.

That worked perfect! Thanks heaps for that. Makes sense now.