Counting with multiple joins

Question

Assuming you have 3 tables: posts, tags, post_tag. There's an m:n relationship between the posts and tags, post_tag is used to connect them.

How would you count the number of posts that have a particular pair of tags? For instance, counting the number of posts that are tagged with both "news" and "featured"?

Edit your question and provide sample data, desired results, and your attempt to solve the problem. — Gordon Linoff, Nov 04 '17 at 16:34

score 0 · Answer 1 · answered Nov 04 '17 at 16:52

Simply use EXISTS for it

select count(*)
from posts
where exists(select 1 from post_tag 
             join tag on post_tag.tid = tag.tid
             where post_tag.pid = posts.pid and tag.name = 'news') and
      exists(select 1 from post_tag 
             join tag on post_tag.tid = tag.tid
             where post_tag.pid = posts.pid and tag.name = 'featured')

Erwin Brandstetter · Accepted Answer · 2017-11-05T00:56:35.280

This is a case of relational division. There are many ways to solve it.
Assuming a standard many-to-many implementation, this should be among the fastest and simplest:

SELECT count(*)
FROM   post_tag pt1
JOIN   post_tag pt2 USING (post_id)
WHERE  pt1.tag_id = (SELECT tag_id FROM tags WHERE tag = 'news')
AND    pt2.tag_id = (SELECT tag_id FROM tags WHERE tag = 'featured');

That's counting posts with at least the two given tags. There can be more tags for the same post.

We have assembled an arsenal of techniques under this related question:

How to filter SQL results in a has-many-through relation

Counting with multiple joins

2 Answers2