CODEWITHSUNDEEP
mysqlsql
Arkuni
Arkuni

Reputation: 154

SQL - Relationship between actors

I have a single table with 2 columns: Actors and movies which looks like this:

ACTOR  | MOVIE
-------+-------------
Volta  | Pulp Fiction
Bruce  | Pulp Fiction
Rhame  | Pulp Fiction
Walke  | Pulp Fiction
Rhame  | Bad Movie
Bruce  | Bad Movie
Volta  | Decent Movie
Brian  | Decent Movie
Walke  | Awesome Movie
Brian  | Awesome Movie

I want to know know which actors, who appeared in Pulp Fiction, never has appeared in another movie with another actor from Pulp Fiction.

From this example, the output should be:

Volta
Walke

Because they appeared in Pulp Fiction and in Decent Movie and Awesome Movie respectively without any other actors from Pulp Fiction.

I'm using MySQL.

Upvotes: 3

Views: 529

Answers (4)

kyooryu
kyooryu

Reputation: 1509

SELECT m.actor
FROM movies m 
WHERE 
m.movie = 'Pulp Fiction'
AND NOT EXISTS
(
  SELECT 1
  FROM movies m1 
    JOIN movies m2 ON m1.movie = m2.movie 
      AND m1.actor <> m2.actor 
      AND m2.movie <> 'Pulp Fiction' 
      AND m2.actor IN (SELECT actor FROM movies WHERE movie = 'Pulp Fiction')
  WHERE
      m.actor = m1.actor 
)

According to SQLFiddle done by ChrisProsser it should give the proper result.

Upvotes: 2

Raj
Raj

Reputation: 716

Try This....

SELECT actor, movie 
FROM actor
where movie like 'Pulp Fiction' AND
movie NOT IN(select movie from tablename t where t.actor like actor and movie NOT LIKE 'Pulp Fiction');

Upvotes: 0

ChrisProsser
ChrisProsser

Reputation: 13088

There may be an easier way, but this should work:

select m.actor 
  from movies m
 where m.movie = 'Pulp Fiction'
   and m.actor not in (
       select m2.actor
         from movies m1,
              movies m2,
              movies m3
        where m1.actor = m2.actor
          and m1.movie = 'Pulp Fiction'
          and m2.movie != m1.movie
          and m3.movie = m2.movie
          and m3.actor in (select m4.actor
                             from movies m4
                            where m4.movie = 'Pulp Fiction')
        group by m2.actor
        having count(*) > 1);

I have created a SQL Fiddle to test this and the output is correct.

Upvotes: 0

davey
davey

Reputation: 1791

SELECT actor FROM table AS t1  
WHERE movie = 'Pulp Fiction' AND  
NOT EXISTS( 
  SELECT 1 FROM table AS t2 WHERE t2.actor = t1.actor AND movie <> 'Pulp Fiction' 
)

I recommend you to store the movies and actors in different tables (a 'movie' table and a 'actor' table) though and use a 'movie_actor' table to connect the data to each other.

Upvotes: -1

Related Questions