I'm working on a movie database and I've got a link table so I can link movies to their sequels. I've written the below query so I can return the list of sequels where the movie_id is in any of the columns of the link table however I was wondering if there was any better way of doing this as it seems a rather long winded way of going about it?
SELECT movie_id, movie_title FROM movies WHERE movie_id in
(SELECT movie_1 FROM lk_movies WHERE movie_1 = 1 or movie_2 = 1 or movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1)
or movie_id in
(SELECT movie_2 FROM lk_movies WHERE movie_1 = 1 or movie_2 = 1 or movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1)
or movie_id in
(SELECT movie_3 FROM lk_movies WHERE movie_1 = 1 or movie_2 = 1 or movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1)
or movie_id in
(SELECT movie_4 FROM lk_movies WHERE movie_1 = 1 or movie_2 = 1 or movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1)
or movie_id in
(SELECT movie_5 FROM lk_movies WHERE movie_1 = 1 or movie_2 = 1 or movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1)
or movie_id in
(SELECT movie_6 FROM lk_movies WHERE movie_1 = 1 or movie_2 = 1 or movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1)
or movie_id in
(SELECT movie_7 FROM lk_movies WHERE movie_1 = 1 or movie_2 = 1 or movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1)
or movie_id in
(SELECT movie_8 FROM lk_movies WHERE movie_1 = 1 or movie_2 = 1 or movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1)
or movie_id in
(SELECT movie_9 FROM lk_movies WHERE movie_1 = 1 or movie_2 = 1 or movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1)
or movie_id in
(SELECT movie_10 FROM lk_movies WHERE movie_1 = 1 or movie_2 = 1 or movie_3 = 1 or movie_4 = 1 or movie_5 = 1 or movie_6 = 1 or movie_7 = 1 or movie_8 = 1 or movie_9 = 1 or movie_10 = 1);
Aucun commentaire:
Enregistrer un commentaire