MySQL Query for Tagging System, Selecting Used Tags Only

Question

I have a tagging system implemented in PHP and MySQL, with the following tables:

Table: blog_tags

• tag_id
• tag_name

Table: blog_tags_assoc

• tag_assoc_id
• article_id
• tag_id

Table: blog_articles

• article_id
• article_title
• article_content

I have them all linked using the Many to Many relationship (reference to something similar: How to store tags in MySQL tags, one field in total or one filed for each tag?)

What I'm trying to do now is make a MySQL query that selects all the tags from blog_tags that have actually been referenced by the articles in blog_articles.

So far, I know that "HAVING" is something I might use…but I'm not sure how? Any ideas or suggestions would be most welcome. Thanks!

Answer 1

If you only want to know the tags that have been used in articles, then you can simply do this:

SELECT DISTINCT tag_id, tag_name
FROM blog_tags
INNER JOIN blog_tags_assoc USING ( tag_id );

Answer 2

This will get a tag name as well as a count of articles that tag has been used.

SELECT
    `blog_tags`.`tag_name`,
    count(*) AS `blog_article_count`
FROM `blog_tags`
    JOIN `blog_tags_assoc`
        ON `blog_tags`.`tag_id`=`blog_tags_assoc`.`tag_id`
    JOIN `blog_articles`
        ON `blog_articles`.`article_id`=`blog_tags_assoc`.`article_id`
GROUP BY `blog_tags`.`tag_name`