返回整个关系列表匹配的所有行(而不是任何组合) [英] Return all rows where the entire list of a relation matches (as opposed to any combination of)

问题描述

具有以下简单的表结构:

With the following simple table structure:

Data
----------
id

Tag
----------
id

TaggedData
----------
id
tag_id
data_id

如果我有一个标签 ID 列表，我将如何获取每一行数据，其中列表中的每一行标签都与匹配的一行数据相关，而不是其中的一个子集?

If I had a list of Tag ids, how would I fetch every row of Data where every row of Tag in my list has a relation to a row of Data matched and not a subset thereof?

这是我现在无法使用的查询:

Here's the query I have right now that doesn't work:

    SELECT
        Data.*

    FROM
        Data
    LEFT JOIN
        TaggedData ON (Data.id = TaggedData.data_id)
    LEFT JOIN
        Tag ON (Tag.id = TaggedData.tag_id)

    WHERE
        Tag.id IN ('1' , '2')

特别是:这个不起作用，因为它会返回与标签 1 或 2 相关的任何数据行.不是 1 和 2.

Specifically: This one isn't working because it will return any row of Data that is related to Tag 1 or 2. Not 1 and 2.

推荐答案

这叫做关系划分"

   SELECT 
        Data.ID  
    FROM 
        Data 
    INNER JOIN 
        TaggedData ON (Data.id = TaggedData.data_id) 
    INNER JOIN 
        Tag ON (Tag.id = TaggedData.tag_id) 
    WHERE 
        Tag.id IN ('1' , '2') 
    HAVING COUNT(DISTINCT tag.iD)=2

这篇关于返回整个关系列表匹配的所有行(而不是任何组合)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！