如何构造SQL查询以查找作为两个特定其他对象的父对象的对象？ [英] How do I structure a SQL query to find an object that is the parent of two specific other objects?

问题描述

说我有2个表，称为父级和子级。父母可以有0到许多孩子，并且孩子可以有1到许多父母。

Say I have 2 tables, called parent and child. A parent can have zero to many children, and a child can have 1 to many parents. How do I find all the parent elements that are parents of two specific children.

例如，假设我有父级[p_a，p_b，p_c，p_d]和子级：[c_a，c_b]
它们具有如下结构：

For instance, say I have Parents [p_a, p_b, p_c, p_d], and children: [c_a, c_b] They have a structure as follows:

• p_a有c_a作为子


• p_b具有c_b作为子


• p_c具有c_a和c_b作为子


• p_d没有子<
  

• p_a has c_a as a child
• p_b has c_b as a child
• p_c has both c_a and c_b as children
• p_d has no children

如何制定查询以选择p_c？

How do I formulate a query to select p_c?

p有[id，name]，c有[id，name]，并且有一个带有[parent_id，child_id]的join_table。 （我也给了它们的顺序ID为这个例子使事情更容易）。

If they had a structure where p has [id, name] and c has [id, name] and there is a join_table with [parent_id, child_id]. (I have also given them sequential id's for this example to make things easier).

我试图解决它想出了以下SQL

My attempt to solve it came up with the following SQL

SELECT p.*
FROM parent AS p
JOIN join_table AS j ON p.id = j.parent_id
JOIN children AS c ON j.child_id = c.id
WHERE c = 1
  OR c = 2

b $ b

但是显然这选择了p_a和p_b以及p_c。我一直在查看 UNION 运算符，但似乎无法使其正常工作。

But obviously this selects p_a and p_b as well as p_c. I've been looking at the UNION operator but can't seem to get it working.

期待您的回答，感谢您的阅读。
mike

Looking forward to your answers, thanks for reading. mike

编辑：提到我用于示例的id约定

mentioned the id convention I'm using for the example

推荐答案

接受的答案的替代方案可能更快：

An alternative to the accepted answer, probably faster:

SELECT p.*
FROM parent p JOIN join_table j ON p.id=j.parent_id
WHERE j.child_id=1 OR j.child_id=2
GROUP BY j.parent_id
HAVING COUNT(j.child_id)=2;

这篇关于如何构造SQL查询以查找作为两个特定其他对象的父对象的对象？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！