How to list all the people who have worked with 'Art Garfunkel'?

I have 3 tables actor, movie, casting. I have to find the people who have worked with actor "ART Garfunkel".

I have solution but I am not able to understand how the following query works.

**movie**   **actor**    **casting**
   id          id           movieid
   title       name         actorid
   yr                       ord
   director     
   budget       
   gross        

SELECT a.name
  FROM (SELECT movie.*
          FROM movie
          JOIN casting
            ON casting.movieid = movie.id
          JOIN actor
            ON actor.id = casting.actorid
         WHERE actor.name = 'Art Garfunkel') AS m
  JOIN (SELECT actor.*, casting.movieid
          FROM actor
          JOIN casting
            ON casting.actorid = actor.id
         WHERE actor.name != 'Art Garfunkel') as a
    ON m.id = a.movieid;

one more solution:

  SELECT actor.name  FROM casting
       JOIN movie ON movie.id=casting.movieid
      JOIN actor ON  actor.id=casting.actorid
     WHERE actor.name !='Art Garfunkel'
    AND
       movie.id IN(SELECT movie.id FROM casting
           JOIN movie ON movie.id=casting.movieid
       JOIN actor ON  actor.id=casting.actorid
        WHERE actor.name='Art Garfunkel')

Easy Solution:

SELECT distinct actor.name
FROM movie
JOIN casting
ON casting.movieid = movie.id
JOIN actor
ON actor.id = casting.actorid
where movie.id in (select movieid from casting join actor on id =actorid where 
actor.name = 'Art Garfunkel') and actor.name <> 'Art Garfunkel'

Here's a simplified version that does the same thing:

select actor.name
from (
    select movieid from casting
    join actor on actor.id = casting.actorid 
        and actor.name = 'Art Garfunkel'
) ag_movies
join casting on ag_movies.movieid = casting.movieid
join actor on actor.id = casting.actorid 
    and actor.name != 'Art Garfunkel'

Yours follows roughly the same steps:

1. Get the movieid for all movies Art Garfunkel was in.
2. Find the cast lists for all those movies.
3. Get the artist names from the cast lists that are not Art Garfunkel.

The casting table is a cross-lookup table which allows many actors to act in many movies. Through the casting table we have to find all the movies Art was in and then reverse that to find the other actors in those movies.

The joins mean the result will only contain rows from both tables which match the join criteria. So on actor.id = casting.actorid and actor.name = 'Art Garfunkel' connects the tables by the matching IDs but limits the results to only those IDs where the actor name is Art's. The join as used in these queries means the same as intersection from set theory.

The sub-select inside the parenthesis creates a temporary table (named ag_movies in this example) that can be used by the parent query just like any other table.

Here is a solution for MySQL:

SELECT name FROM actor JOIN casting ON actor.id = actorid JOIN movie ON movie.id = movieid WHERE movie.id IN (
SELECT movieid FROM casting 
WHERE actorid IN (SELECT id FROM actor WHERE name  = 'Art Garfunkel') 
) AND actor.name != 'Art Garfunkel';