oracle - NOT IN with Subquery SQL Construct -

actor (id, fname, lname, gender) movie (id, name, year, rank) casts (pid, mid, role) pid references actor id mid references movie id 

list movies x has been in without y (x , y actors).

i finding difficult construct sql not in. attempt. im unable fininsh off due second actor not being present

select m.name movie m m.id not in (select c.mid                        casts c, actor                        c.pid = a.id , a.name = "adam..")  

using not exists:

select m.name                       -- show names                       movie m                        -- of movies exists                        -- there       ( select *                    -- role         casts c                -- casted           join actor              -- actor             on c.pid = a.id         c.mid = m.id             , a.name = 'actor x'    -- name x       )    , not exists                    -- , there not       ( select *                    -- role         casts c                -- casted           join actor              -- actor             on c.pid = a.id         c.mid = m.id            , a.name = 'actor y'    -- name y       ) ; 

you can use not in. note may give unexpected results if there rows null in movie.id or casts.mid column:

select m.name                       -- show names                       movie m                        -- of movies m.id in                       -- keep movies       ( select c.mid                -- movies         casts c                -- had role casted           join actor              -- actor             on c.pid = a.id         a.name = 'actor x'    -- name x       )    , m.id not in                   -- , not movies       ( select c.mid                --         casts c                -- had role casted           join actor              -- actor             on c.pid = a.id         a.name = 'actor y'    -- name y       ) ;