CODEWITHSUNDEEP
sqloracle-database
mitchell katz
mitchell katz

Reputation: 1

Check if tuple is in every subquery result (Oracle SQL)

I am trying to find every section that was held in every classroom that has more than 130 seats. Right now this just finds all sections that were held in at least one classroom with more than 130 seats. 

select * 
from section 
where (building, room_number) in (select building,room_number 
                                  from classroom where capacity > 130)

Database scheme

Upvotes: 0

Views: 636

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270703

You can use `not exists

select * 
from section s 
where not exists (select 1
                  from classroom c
                  where c.building = s.building and 
                        c.room_number = s.room_number and
                        c.capacity <= 130
                 ) ;

Upvotes: 0

zip
zip

Reputation: 4061

You can use having to achieve that:

    select sec_id, count(*) 
    from section a inner join
    (
     select building, room_number
     from 
     ( 
      select distinct building,room_number 
       from classroom where capacity > 130
     ) a
    )b on a.building = b.building  and a.room_number = b.room_number

and 

    having count(*) = ( select count(*) 
                        from
                        (select distinct building,room_number 
                        from classroom where capacity > 130)a
                       )
    Group by sec_id

Upvotes: 0

Related Questions