Complicated Join

[selliottsxm]

I am having a devil of a time with joining up my tables.  I have 7 tables:

 

movies, studios,scenes are all joining up fine using this query:

 

SELECT movies.movie_id, movies.movie_title, movies.movie_desc, studio, scene_name FROM movies INNER JOIN studios ON movies.studio_id=studios.studio_id INNER JOIN scenes ON movies.movie_id=scenes.movie_id ORDER BY movie_id ASC, scene_name ASC

 

i am trying to join up the remaining four which is where I am having problems.

 

The tables are:

 

actors

movie_actor (movie_actor being the child and related to movies by the movie_id

 

genres

movie_genres (movie_genres being the child and related to movies by the movie_id

 

I have tried joining up actors and genres.  In the query below I tried with the  genres table, then tried with the actors table. The error result is the same with the exception of the field name:

 

SELECT movies.movie_id, movies.movie_title, movies.movie_desc, studio, scene_name, genre FROM movies INNER JOIN studios ON movies.studio_id=studios.studio_id INNER JOIN scenes ON movies.movie_id=scenes.movie_id
INNER JOIN movie_genres ON movies.movie_id=movie_genres.movie_id ORDER BY movie_id ASC, scene_name ASC

 

returns an error: unknown column 'genre' in the field list.

 

Does anyone know what I might be doing wrong?

 

 

[Antonio Conte]

SELECT movies.movie_id, movies.movie_title, movies.movie_desc, studio, scene_name, genre 

FROM movies 
INNER JOIN studios ON ( movies.studio_id = studios.studio_id )
INNER JOIN scenes ON ( movies.movie_id = scenes.movie_id )
INNER JOIN movie_genres AS genres ON ( movies.movie_id = genres.movie_id )
INNER JOIN movie_actors AS actors ON ( movies.movie_id = actors.movie_id )

ORDER BY movie_id ASC, scene_name ASC

Structure your queries more to spot errors. SQL is a lot easier to read when you do that.

 

The error message is very revealing here. Make sure the column is actually called 'genre'. If that doesn't work, make sure you actually have a match for the movie_id in the movie_genres table. As a simple test, try switching the 'INNER JOIN ...' to 'LEFT OUTER JOIN ...' and see if you get a null value in the genre column.

 

Hope you solve it.

[selliottsxm]

Hi Antonio,

 

Thanks very much for the help.  Didn't work though.  Taking out the genre part and replacing it with actor names gave me the same result.  Left outer join didn't make a difference either.  I think that I have the relationships correct.  Back to the drawing board!

 

Steve

[Antonio Conte]

Did you try my LEFT OUTER JOIN suggestion?

 

This doesn't make sense if you have done everything right, so I'm thinking the error here is as basic as a misspelling or similar. Look closely at the actual tables to make sure they are actually there (dev-vs-prod environment might be different) and that you haven't called it 'genres' or 'genereName' instead of genre.

[selliottsxm]

Scratching my head too.  Left outer join didn't work.  All spelling is correct and table have all the fields.

Going to leave it for a day and look at it again.  Thanks again Antonio, very nice of you to help