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

问题描述

这与此问题密切相关，但是添加了另一个要求.

This is closely related to this question, but adds another requirement.

给出父表"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  ║
╚════════════╩══════════╝

如何选择具有全部且仅 套指定关系的所有父母?例如.通过样本数据，我如何找到与属性5和1完全有关系的所有父母?

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

推荐答案

SELECT PARENT_ID
FROM rel
GROUP BY PARENT_ID
HAVING SUM(PROP_ID NOT IN (5,1)) = 0
AND SUM(PROP_ID = 1) = 1 
AND SUM(PROP_ID = 5) = 1

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