MySQL - how do I write this Query

Question

I am very new to SQL and I wanted some help in writing this SQL query.

I have a movie table like this:

Movie_id Actor_name  Director_name
1         a             AA
1         b             AA
2         b             BB
2         d             BB

Now I want to print out all the pairs of actors and directors who have not worked with each other - for eg in this case it will print out (a,BB) and (d,AA)

Answer 1

You can do it like this:

SELECT a.Actor_name, d.Director_name
FROM (
  (SELECT DISTINCT Actor_name from movie) a
  CROSS JOIN
  (SELECT DISTINCT Director_name from movie) d
) WHERE NOT EXISTS (
  SELECT * FROM movie m
  WHERE m.Actor_name=a.Actor_name
    AND m.Director_name=d.Director_name
)

The idea is to produce all possible pairs of actors and directors (the cartesian product join in the middle of the query), then filter its results to exclude pairs connected through a movie (the NOT EXISTS condition).

Here is a demo on sqlfiddle.