0

So I have three separate tables labeled directors, studios, and movie.

Directors columns: id, name, dob, nationality, gender.

Studios columns: id, name, founding date, and director_id (Foreign Key is director_id)

Movies: ID, name, length, studio_id (foreign key is studios_id)

I have to find all the directors who directed movies with the name awakening but I have no idea how to use foreign keys properly. Best I've come up with was

SELECT name, studio_id FROM studios WHERE name = "awakening"

I have no idea what I'm doing. Please help.

PoX
  • 1,229
  • 19
  • 32
Ian
  • 1
  • 1
  • 2
    smells like homework – pje Jan 15 '16 at 03:37
  • 1
    I'm not sure why your schema links "studio" and "director" (probably just what your professor set up when he made the homework assignment) but what you're looking for is a [join](http://www.w3schools.com/sql/sql_join_left.asp): http://stackoverflow.com/questions/3709560/mysql-join-three-tables – paulsm4 Jan 15 '16 at 03:38
  • Haha, close. I'm reviewing for a test and realized I had forgotten databases stuff over the break. – Ian Jan 15 '16 at 03:47
  • Thanks for the link paul. – Ian Jan 15 '16 at 03:48

2 Answers2

0

Please try to use INNER JOIN on your query

SELECT d.id, d.name FROM director d INNER JOIN studios s ON d.id = s.director_id INNER JOIN movie m ON s.id = m.studio_id WHERE m.name = "awakening";
John Ephraim Tugado
  • 757
  • 7
  • 15
0
SELECT Movies.name as movie_name, Directors.name as director_name 
FROM Movies
JOIN Studios ON (Movies.studio_id = Studios.id)
JOIN Directors ON (Studios.director_id = Directors.id)
WHERE Movies.name = 'awakening';
Gaurav Lad
  • 1,788
  • 1
  • 16
  • 30