当所有子记录均满足条件时，仅选择父记录 [英] Select only parent records when all children records meet conditions

问题描述

我有两个表A和B，当所有子项(在表B中)都满足条件时，我只希望父项A的行.如果来自B的一行不符合条件，那么我不需要父A的行.我想我需要在这里使用存在的，但不展示如何使用.

I have two tables A and B where I only want the parent A's rows when all the children (in Table B) meet the criteria. If one row from B does not meet the criteria, then I don't need parent A's row. I think I need to use exists here, but not show how.

以下是数据表:

表A

Primary Key Level
1   low
2   low
3   high 
4   high 
5   low

表B

Primary Key Phase   Parent Primary Key
1   open    1
2   open    1
3   close   1
4   close   2
5   close   2
6   close   3
7   open    4
8   open    4
9   open    5
10  close   5

我正在尝试的查询:

select * 
from table_a, table_b
where table_a.level = 'low' and
      table_b.phase = 'close' and
      table_a.primary_key=table_b.parent_primary_key

但是我的查询还会返回table_a.primary_key = 5的行.

but my query would also return rows where the table_a.primary_key = 5.

基本上，我想要返回的唯一行是在table_A.primary_key = 2时，因为该级别较低，并且两个子行的相位都等于关闭.

Basically the only rows I want returned is when table_A.primary_key = 2 beause the level is low, and both children rows have a phase equal to close.

谢谢！

推荐答案

或者:

select a.*
from table_a a
where a.level = 'low' and
      'close' = all (select phase
                  from table_b b
                  where b.parent_primary_key = a.primary_key
                 );

这篇关于当所有子记录均满足条件时，仅选择父记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！