February 6th, 2013, 04:51 AM
JOIN count wont show items with 0 results
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
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
What I'm trying to get it
Am I trying to do this the wrong way?
Any help would be appreciated.
February 6th, 2013, 05:22 AM
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.
SELECT T.tag, COUNT(C.tag_id) AS num FROM tags T LEFT JOIN comment_tags C ON T.id = C.tag_id GROUP BY T.tag
Comments on this post
February 6th, 2013, 06:15 AM
That worked perfect! Thanks heaps for that. Makes sense now.
Originally Posted by requinix