如何选择与所有必需标签匹配的帖子? [英] How do I select posts that match all the required tags?

问题描述

我有一个包含两列的表格，博客文章 ID 和标签 ID.一篇博客文章可以有多个标签，比如一篇关于纽约"和苏荷"的文章.假设我想找到所有标签 ID 为 1 和 2 的博客文章，我该怎么做?

I have a table with two columns, blog post ids and tag ids. A blog post can have several tags like it's a post about "New York" and "Soho". Let's say I want to find all the blog posts that have BOTH tag id 1 and 2, how do I do that?

blogPostToTags
=============================
id           |tagId
-----------------------------
1            |1
1            |2
2            |1
3            |2

更新:

我尝试了以下方法:

SELECT id FROM blogPostToTags WHERE tagId = 1 AND tagId = 2

和

SELECT id FROM blogPostToTags WHERE tagId = 1 OR tagId = 2

不管用...

推荐答案

使用双JOIN.

SELECT id
FROM blogPosts p
INNER JOIN blogPostToTags pt1 ON pt1.id = p.id
INNER JOIN blogPostToTags pt2 ON pt2.id = p.id
WHERE pt1.tagId = 1 AND pt2.tagId = 2;

这篇关于如何选择与所有必需标签匹配的帖子?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！