Ewan Lyle Ewan Lyle - 5 months ago 6
SQL Question

Select from table where value in column A or column B

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);

Answer

Unfortunately Mysql does not support Unpivot or Cross Apply operator so here is the best I came up with

The sub-query

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 

can be re-written by reversing the IN operator

SELECT movie_10 
FROM   lk_movies 
WHERE  1 IN( movie_1, movie_2, movie_3, movie_4, 
             movie_5, movie_6, movie_7, movie_8, 
             movie_9, movie_10 ) 

Then use Union all instead of multiple OR condition here is the full query

 SELECT movie_id, 
       movie_title 
FROM   movies 
WHERE  movie_id IN (SELECT movie_1 
                    FROM   lk_movies 
                    WHERE  1 IN( movie_1, movie_2, movie_3, movie_4, 
                                 movie_5, movie_6, movie_7, movie_8, 
                                 movie_9, movie_10 ) 
                    UNION ALL 
                    SELECT movie_2 
                    FROM   lk_movies 
                    WHERE  1 IN( movie_1, movie_2, movie_3, movie_4, 
                                 movie_5, movie_6, movie_7, movie_8, 
                                 movie_9, movie_10 ) 
                    UNION ALL 
                    ...............
                    ...............
                    SELECT movie_10 
                    FROM   lk_movies 
                    WHERE  1 IN( movie_1, movie_2, movie_3, movie_4, 
                                 movie_5, movie_6, movie_7, movie_8, 
                                 movie_9, movie_10 )) 
Comments