CODEWITHSUNDEEP
sqloracle-databaseexists
confusedbeginner
confusedbeginner

Reputation: 131

Select only parent records when all children records meet conditions

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.

Here are the data tables:

Table A 

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

Table 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

And the query I was trying: 

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

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

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.

Thank you!

Upvotes: 2

Views: 2828

Answers (2)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36977

Alternatively:

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
                 );

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Is this what you want

select a.*
from table_a a
where a.level = 'low' and
      not exists (select 1
                  from table_b b
                  where b.parent_primary_key = a.primary_key and
                        b.phase <> 'close'
                 );

The not exists is a double negative. It checks that there are no children with a phase other than 'close' -- which is basically equivalent to saying that all children are 'close'. (If NULL values are allowed, the logic is not exactly equivalent.)

Upvotes: 5

Related Questions