从多个值中选择MySQL SELECT，仅返回完全匹配项 [英] MySQL SELECT from multiple values, return only exact matches

问题描述

我有一个查询，该查询采用从先前查询中获得的标签ID，并从post_tag_map表中选择帖子ID.

I have a query that takes tag Ids obtained from a previous query, and selects post ids from a post_tag_map table.

$ tagIds可以类似于"3,4,23,54".

$tagIds for example can be something like "3,4,23,54".

'SELECT post_id FROM post_tag_map WHERE tag_id IN (' . $tagIds . ') ORDER BY `post_id`;'

此查询可以很好地实现其目的，但是它返回具有其中任何一个tag_id的post_id.

This query works fine for its purpose, but it returns any post_id that has any one of those tag_ids.

我正在尝试仅获取包含所有这些tag_id的post_id.

I'm trying to obtain only post_ids that contain ALL of those tag_ids.

例如，如果我搜索新闻，政治，竞选"，我只想返回已被所有这三个标签标记的帖子，而不是任何包含这三个标签之一的帖子.

For example, if I search for "news, politics, campaign", I only want to return posts that have been tagged with all three of those tags, not any post that has one of the three.

如何更改查询?

根据要求，我的表结构为3个表.一个具有post_id和post信息的(post)，一个具有tag_id和tag_name的(tag)，以及post_tag_map，仅将两个ID映射在一起.

By request, my table structure is 3 tables. One (post) with post_id and post information, one (tag) with tag_id and tag_name, and post_tag_map, just maps the two Ids together.

推荐答案

嗯，它将变得更加复杂

保留您的IN内容，但也可以对帖子进行分组以查找完全匹配的帖子

Keep with your IN thing but also group posts to find ones with exact match

WHERE tag_id IN (3,4,23,54) GROUP BY `post_id` HAVING count(1) = 4;

其中4是我们正在寻找的标签

where 4 is a number of tags we're looking for

这篇关于从多个值中选择MySQL SELECT，仅返回完全匹配项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！