can you help on this query?
I am new to sql and cannot work out the query for this:
>>>List the films in which 'Harrison Ford' has appeared <<<
movie(id, title, yr, score, votes, director)
actor(id, name)
casting(movieid, actorid, ord)
The whole exercises can be found on http://sqlzoo.net/3.htm
thanks in advance
A. :)
Software/Hardware used:
Software/Hardware used:
ASKED:
January 19, 2009 11:49 PM
UPDATED:
January 20, 2009 4:22 PM
Answer Wiki:
-In movie table, you have information about movies.
-In actor table, you have information about actors.
-And in casting table, you have information on what actors appeared on each movie.
So, if you want to know in what films Harrison Ford has appeared, you need to look at casting.
But in casting you find just codes, and you do not have the actor id for ‘Harrison Ford’, so in order to be able to search by the name of the actor you need to join the actor table.
Joining these two tables you can get the list of films in which Harrison Ford appeared, this way:
<pre>SELECT c.movieid
FROM actor AS a
JOIN casting AS c
ON a.id = c.actorid
WHERE a.name = "Harrison ford"
</pre>
But as you see, this query is showing the id of the movies, not the movie’s title, so, If you want to display the title, you need to join the movie table, this way (you might need to change double quotes by single ones depending on the dbms) :
<pre>SELECT m.title
FROM actor AS a
JOIN casting AS c
ON a.id = c.actorid
JOIN movie AS m
ON c.movieid = m.id
WHERE a.name = "Harrison ford"
</pre>
Hope this helps.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
