can you help on this query?

5 pts.
Tags:
JOIN statement
SQL
SQL 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. :)

Answer Wiki

Thanks. We'll let you know when a new response is added.

-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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following