Hi Guys,
I'm wanting to join four tables, or at least this is what i think i need to do.
These are the table structures:
movie_table
movie_id
movie_title
genre
genre_id
genre_parent
genre_name
actor
actor_id
first_name
last_name
director
director_id
first_name
last_name
movie_genre
movie_id
genre_id
movie_actor
actor_id
movie_id
movie_director
director_id
movie_id
This is the query i have so far:
$query = "SELECT * FROM movie, movie_genre WHERE movie.movie_id=movie_genre.movie_id AND movie_genre.genre_id='$new_genre_id' ORDER BY movie_title DESC LIMIT $limitvalue, $limit";
Obviously this only joins the movie table to the movie_genre table.
What i would now like to add is the directors 'first_name', 'last_name' and the actor(s) 'first_name', 'last_name' to the query, keeping in mind that a movie might have several actors associated with the film.
Any ideas greatly appreciated.
And i have searched the boards for quite some time but nothing to lead me in the right direction with my situation.
Cheers,
micmac
