Select from table where value in column A or column B

Question

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 1

When you create a table with columns that have a number attached, you're almost certainly doing it wrong. Your table is not normalized properly.

All code below is untested since I don't use mysql. It may have some syntax errors, but you should get the idea.

You could create a tables like this:

CREATE TABLE Movie
| ID | Title   | ... | Sequel |
|  1 | Shrek   | ... | 2      |
|  2 | Shrek 2 | ... | 3      |
|  3 | Shrek 3 | ... | NULL   |

That way you'd only know the next sequel, and you'd have to have to follow the Sequel column pointing to the next ID until you reach a Movie with a NULL in Sequel.

SELECT A.Titel, B.Titel AS Sequel
FROM Movie A
INNER JOIN Movie B
ON A.Sequel = B.ID

To find all sequels in one query, consider a separate table for the sequels. That also removes the limitation on the number of sequels.

Movie
| ID | Title    | ... |
|  1 | Shrek    | ... |
|  2 | Shrek 2  | ... |
|  3 | Shrek 3  | ... |

and

Sequel
| Movie_ID | Sequel_ID |
|        1 |         2 | 
|        1 |         3 |
|        2 |         3 |

Then you can select from that

SELECT Movie.Titel 
FROM Movie 
INNER JOIN Sequel
ON Movie.Movie_ID = Sequel.Sequel_ID
INNER JOIN Movie S
ON Sequel.Sequel_ID = S.Movie_ID

The drawback here is that you duplicate information, i.e., you need to store for Shrek that there is Shrek 2 and Shrek 3 as sequels, and for Shrek 2 you duplicate the fact that Shrek 3 is a sequel of Shrek 2. Unless, of course, you always want to only search from the very first movie.

Note the corner case for Highlander, which cannot have sequels. There can be only one.

Answer 2

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