NOT IN with Subquery SQL Construct

ACTOR (id, fname, lname, gender)
MOVIE (id, name, year, rank)
CASTS (pid, mid, role)
WHERE pid references ACTOR id
mid references Movie id

List the movies that x has been in without y (x and y are actors).

I am finding it difficult to construct an SQL with NOT in. This is my attempt. Im unable to fininsh it off due to the second actor not being present

SELECT m.name
FROM MOVIE m
WHERE m.id NOT IN (SELECT c.mid
                       FROM CASTS c, ACTOR a
                       WHERE c.pid = a.id AND a.name = "adam..") 

Answers


Using NOT EXISTS:

SELECT m.name                       -- Show the names                      
FROM movie m                        -- of all movies
WHERE EXISTS                        -- that there was
      ( SELECT *                    -- a role
        FROM casts c                -- casted to
          JOIN actor a              -- actor with
            ON c.pid = a.id
        WHERE c.mid = m.id  
          AND a.name = 'Actor X'    -- name X
      ) 
  AND NOT EXISTS                    -- and there was not
      ( SELECT *                    -- any role
        FROM casts c                -- casted
          JOIN actor a              -- to actor with
            ON c.pid = a.id
        WHERE c.mid = m.id 
          AND a.name = 'Actor Y'    -- name Y
      ) ;

You can also use NOT IN. Note that this may give you unexpected results if there are rows with NULL in the movie.id or casts.mid column:

SELECT m.name                       -- Show the names                      
FROM movie m                        -- of all movies
WHERE m.id IN                       -- but keep only the movies that
      ( SELECT c.mid                -- movies that
        FROM casts c                -- had a role casted to
          JOIN actor a              -- actor with
            ON c.pid = a.id
        WHERE a.name = 'Actor X'    -- name X
      ) 
  AND m.id NOT IN                   -- and not the movies
      ( SELECT c.mid                -- that
        FROM casts c                -- had a role casted
          JOIN actor a              -- to actor with
            ON c.pid = a.id
        WHERE a.name = 'Actor Y'    -- name Y
      ) ;

Need Your Help

How to make a simple fetch from relationship entities? Swift

ios swift core-data nsfetchedresultscontrolle

I made two relationship entities in Xcode, I use the Swift. I made a simple relationship in Core Data.

Check if an element has event listener on it. no jquery

javascript web

how to check if an element has event listener on it, if i use an inline function on it like the code below. because i have a function that recalls the function and add the event listener but it cau...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.