CODEWITHSUNDEEP
mysql
user55776
user55776

Reputation:

Many-to-one relationship to implement tags: How to count how many times a tag is used?

I'm experimenting with a tagging system which is a many-to-one relationship. My schema is:

items table:

comment _ tags:

tags table:

I've been reading of implementation designs at the links at the bottom but got stuck. I can insert tags without a problem.

How do I fetch every tag that has been used and get how many times it has been applied to an item in the comment _ tags table?

http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html

SQL Query for Product-Tag relationship

Upvotes: 0

Views: 522

Answers (2)

DForck42
DForck42

Reputation: 20367

not the correct names, but mostly correct syntex

select TagName, count(TagName)
from TagTable
group by TagName

Upvotes: 0

Chad Birch
Chad Birch

Reputation: 74588

SELECT t.tag_name, COUNT(*)
FROM tags AS t
    INNER JOIN comment_tags AS c_t ON c_t.tag_id = t.tag_id
GROUP BY c_t.tag_id
ORDER BY t.tag_name;

Upvotes: 1

Related Questions