联接中所有记录都符合条件的SQL查询? [英] SQL query where ALL records in a join match a condition?

问题描述

我似乎有一个简单的问题，但无法通过SQL找出正确的解决方案.我专门使用了postgresql.

I have what seems to be a simple problem, but can not figure out the proper solution via SQL. I'm using postgresql specifically.

请执行以下操作:

SELECT * FROM users INNER JOIN tags ON (tags.user_id = users.id) WHERE tags.name IN ('word1', 'word2')

这不能满足我的需求.我想查找其标签仅包含在列表中的用户.如果用户具有不在列表中的标签，则不应包括该用户.

This does not do what I need. I want to find users whose tags are ONLY included in the list. If the user has a tag that is not in the list, the user should not be included.

'user1'标签:word1，word2，word3
'user2'标签:word1
'user3'标签:word1，word2

'user1' tags: word1, word2, word3
'user2' tags: word1
'user3' tags: word1, word2

给出:word1和word2.我想准备一个返回"user2"和"user3"的查询.排除了"user1"，因为它具有不在列表中的标签.

Given: word1 and word2. I want to prepare a query that returns 'user2' and 'user3'. 'user1' is excluded because it has a tag that is not in the list.

希望我已经明确了这一点.感谢您的帮助！

Hopefully I made this clear. Thanks for your help!

推荐答案

依靠COUNT(*)= 2将要求标签表中不能包含user_id和name的重复项.如果是这样，我会选择那条路线.否则，这应该可行:

Relying on COUNT(*) = 2 will require that there can be no duplicates of user_id and name in the tags table. If that's the case, I'd go that route. Otherwise, this should work:

SELECT u.* 
FROM users AS u
WHERE u.id NOT IN (
    SELECT DISTINCT user_id FROM tags WHERE name NOT IN ('word1', 'word2')
) AND EXISTS (SELECT user_id FROM tags WHERE user_id = u.id) 

这篇关于联接中所有记录都符合条件的SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！