The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-16. For the latest, interactive version please visit https://mariadb.com/kb/.

Getting Result for Counted and Not Counted Record.

I have 3 tables.

tags, posts and poststags

The poststags table hold tags.id and posts.id so I can assign multiple tags to a post.

I wanted to count the total number of tags used including zero (0) count.

Sample result:
tag A - count = 5
tag B - count = 10
tag C - count = 0
tag D - count = 25

etc...

All I can think of is create two statements:

SELECT * FROM tags;

Then while traversing each record:

SELECT COUNT(pt.tagid) FROM poststags pt
LEFT JOIN posts p ON p.id = pt.postid
WHERE p.published = 'yes' AND pt.tagid = /* current tag id from first statement */

Is it possible to create 1 single SQL statement instead of 2 and get the same result?

Answer Answered by Daniel Black in this comment.

SELECT t.name, COUNT(*)
FROM tags t
LEFT JOIN poststags pt ON pt.tag_id = t.id
GROUP BY t.id

LEFT JOIN is so 0 count tags can be achieved.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.