SQL仅选择存在多个关系的行 [英] SQL Select only rows where multiple relationships exist

问题描述

给出父表"parent"

Given a parent table 'parent'

╔═══════════╦══════════╗
║ PARENT_ID ║   NAME   ║
╠═══════════╬══════════╣
║         1 ║ bob      ║
║         2 ║ carol    ║
║         3 ║ stew     ║
╚═══════════╩══════════╝

以及父表与(此处未指定)属性表之间的多对多关系表"rel"

and a many-many relationship table 'rel' between parent and a (here unspecified) property table

╔═══════════╦═══════════╗
║ PARENT_ID ║  PROP_ID  ║
╠═══════════╬═══════════╣
║         1 ║         5 ║
║         1 ║         1 ║
║         2 ║         5 ║
║         2 ║         4 ║
║         2 ║         1 ║
║         3 ║         1 ║
║         3 ║         3 ║
╚═══════════╩═══════════╝

如何选择具有 all 个指定关系集的所有父母?例如.通过样本数据，我如何找到同时具有属性5和1的所有父母?

How can I select all parents that have all of a specified set of relationships? E.g. with the sample data, how can I find all parents that have both property 5 and 1?

同样的问题，但要求完全匹配: SQL仅选择存在确切多个关系的行

edit: Same question but with requirement for an exact match: SQL Select only rows where exact multiple relationships exist

推荐答案

这称为

This is called Relational Division

SELECT  a.name
FROM    parent a
        INNER JOIN rel b
            ON a.parent_ID = b.parent_ID
WHERE   b.prop_id IN (1,5)
GROUP BY a.name
HAVING COUNT(*) = 2

• SQLFiddle演示链接
• SQLFiddle Demo Link

更新1

如果不是每个parent_id都在prop_id上强制执行唯一约束，则在这种情况下需要DISTINCT.

if unique constraint was not enforce on prop_id for every parent_id, DISTINCT is needed on this case.

SELECT  a.name
FROM    parent a
        INNER JOIN rel b
            ON a.parent_ID = b.parent_ID
WHERE   b.prop_id IN (1,5)
GROUP BY a.name
HAVING COUNT(DISTINCT b.prop_id) = 2

这篇关于SQL仅选择存在多个关系的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！