Ok heres the scenario:
3 tables
Movie - MovieID, MovieName, Starring1, Starring 2, Director
Director - MovieID, DirectorID, DirectorName
Actors - MovieID, ActorName
In bold are the names of the tables --
in bold+italic are the PK - Primary Keys.
Ive tried to create a relationship between:
1. Movie - MovieID to Director -MovieID - Movie is related to the director
2. Actors - MovieID to Movie-MovieID - Actor is related to Movie
Second relationship failed for some reason.
Im trying to achieve this result:
Movie = Pulp Fiction
Director = Quentin Tan
Actor = Sam L Jackson
Once i do reports etc.....What am i doing wrong here as i think its my relationships im having difficulty with?
Thank you in advance
try:
Select movieName,
DirectorName,
actorName
from movie m
join director d
on m.movieId = d.movieId
and m.movieName = 'Pulp Fiction'
and d.directorName = 'Quentin Tan'
join actor a
on m.movieId = a.movieId
and a.actorName = 'Sam L Jackson'
an alternative is:
|||Thanks but am i on the right lines about creating these relationships in the way i described or would you do anything differently?|||Select movieName,
DirectorName,
actorName
from movie m
join director d
on m.movieId = d.movieId
join actor a
on m.movieId = a.movieId
where m.movieName = 'Pulp Fiction'
and d.directorName = 'Quentin Tan'
and a.actorName = 'Sam L Jackson'
Assuming that a movie has only one director, something like this:
Director -- DirectorID (PK), DirecorName
Actor -- ActorID (PK), ActorName
Movie -- MovieID (PK), MovieName, DirectorID
Cast -- MovieID, Order, Part, ActorID, (PK: MovieID, ActorID, Part)
The "Cast" table is necessary so that you can list all of the actors of the movie. I added in "Order" so that you can control "Promotion Order" or "Billing Order" or whatever you want to call it -- it is a control so that you can control the order in which the actors might be listed in credits.
I added in "Part" for movies in which somebody like Eddie Murphy might play multiple parts.
From my perspective the biggest problem here is the "Cast" table as I have made it. I still don't feel that it is general enough. For instance, say that you want to use this as the basis for listing movie credits. You want to include Producers, stunt coordinators, etc. It would be best to generalize the "Cast" table into a "Role" table and along the way establish "Subcategories" of the "Role" table. But now we are getting ahead of ourselves. This should get you started.
