#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2005
    Posts
    125
    Rep Power
    9

    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.
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,965
    Rep Power
    9397
    JOIN count wont show items with 0 results
    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,
    Code:
    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
    Besides the added "LEFT" it's the same query as before.

    Comments on this post

    • doush. agrees
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2005
    Posts
    125
    Rep Power
    9
    Originally Posted by requinix
    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,
    Code:
    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
    Besides the added "LEFT" it's the same query as before.
    That worked perfect! Thanks heaps for that. Makes sense now.

IMN logo majestic logo threadwatch logo seochat tools logo