MySQL 联接 - 仅当所有右表行都满足 WHERE 子句时，才检索左表行

Question

>我有两个表(表A和表B(，其中"表A"中的单个父行将在"表B"中具有多行。我必须从"表 A"中检索行，仅当"表 B"中的所有子行都满足 WHERE 子句时。

表 A

id     INT
name    VARCHAR
gender  VARCHAR

表 B

id        INT
table_A_id INT
condition  INT

现在，我必须为"表 B"中的所有子行'condition=100'中所有子行都满足WHERE子句的行获取"表 A"的行。

Answer 1

此查询：

select table_A_id
from tableb
group by table_A_id
having sum(case when condition = 100 then 1 else 0 end) = count(*)

返回表 B 中满足条件的所有table_A_id。
您可以像这样将其与 IN 一起使用：

select * 
from tablea 
where id in (
select table_A_id
from tableb
group by table_A_id
having sum(case when condition = 100 then 1 else 0 end) = count(*)
)

或者，您可以加入子查询：

select a.* 
from tablea a inner join (
select table_A_id
from tableb
group by table_A_id
having sum(case when condition = 100 then 1 else 0 end) = count(*)
) b on b.table_A_id = a.id

请注意，对于 MySql，HAVING 子句可以简化为：

having sum(condition = 100) = count(*)

Answer 2

这将这样做：

select * from TableA A JOIN TableA B ON B.table_A_id = A.id 
where not exists (select 1 from TableB B where B.table_A_id = A.id and condition  <> 100)

Answer 3

我只是推荐：

select a.*
from TableA a 
where not exists (select 1
from TableB B
where B.table_A_id = A.id and
condition <> 100
);

如果condition可以NULL，则需要：

where not exists (select 1
from TableB B
where B.table_A_id = A.id and
(condition <> 100 or condition is null)
);