看起来很简单，但我很难过！ [英] Looks simple, but I'm stumped!

问题描述

这应该很简单，但我在画一个空白.我有两张桌子.

This should be so simple, but I'm drawing a blank. I have two tables.

Table:article_tag_pvt
colum: article_id
column: tag_id

Table: tag
column: tag_id
column: name

article_tag_pvt 表是一个多对多数据透视表.

The article_tag_pvt table is a many-to-many pivot table.

问题是，我需要一个给定标签名称列表的查询，将检索仅匹配这些标签名称的文章 ID.这些表的基本连接如下所示:

The problem is, I need a query which given a list of tag names, will retrieves article Ids which ONLY match those tag names. The basic joining of these tables looks like this:

SELECT article_id
FROM article_tag_pvt pvt
INNER JOIN tag t ON t.tag_id = pvt.tag_id

我已经有了一个查询，它将检索与指定的任何标签名称相匹配的文章 ID.这看起来像这样:

I've already got a query which will retrieve article ids which match ANY tag names specified. This looks like this:

SELECT article_id
FROM article_tag_pvt pvt
INNER JOIN tag t ON t.tag_id = pvt.tag_id
WHERE t.name IN ('events','news')

我已经尝试过这些，但并不高兴:

I've tried these but no joy:

SELECT article_id
FROM article_tag_pvt pvt
INNER JOIN tag t ON t.tag_id = pvt.tag_id
WHERE t.name = 'events' AND t.name = 'news'

SELECT article_id
FROM article_tag_pvt pvt
INNER JOIN (
SELECT tag_id
FROM tag
WHERE name IN ('events','news')
) AS t
ON t.tag_id = pvt.tag_id

任何帮助将不胜感激

戴夫

推荐答案

问题是您得到所有​​包含事件或新闻的行.如果您在多对多表中从来没有超过 1 个事件或新闻，您可以使用 GROUP BY 和 HAVING 来解决它

The problem is that you get ALL rows which have either event or news. If you never have more than 1 event or news in many to many table you can use GROUP BY and HAVING to solve it

SELECT article_id
FROM article_tag_pvt pvt
INNER JOIN tag t ON t.tag_id = pvt.tag_id
WHERE t.name = 'events' OR t.name = 'news' 
GROUP BY article_id HAVING count(*) = 2

这篇关于看起来很简单，但我很难过！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！