selliottsxm Posted May 21, 2014 Share Posted May 21, 2014 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_idINNER 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? Link to comment Share on other sites More sharing options...
Antonio Conte Posted May 22, 2014 Share Posted May 22, 2014 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. Link to comment Share on other sites More sharing options...
selliottsxm Posted May 22, 2014 Author Share Posted May 22, 2014 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 Link to comment Share on other sites More sharing options...
Antonio Conte Posted May 22, 2014 Share Posted May 22, 2014 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. Link to comment Share on other sites More sharing options...
selliottsxm Posted May 22, 2014 Author Share Posted May 22, 2014 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 Link to comment Share on other sites More sharing options...
Recommended Posts