MySQL WHERE IN Query - ORDER BY Match
You can do do this by following way:
SELECT distinct movie FROM genre_rel WHERE genre IN (1, 8, 3);
If I understand correctly you want to sort results by number of matches in descending order. To do so, you might try:
SELECT movie
FROM genre_rel
WHERE genre IN (1, 8, 3)
GROUP BY movie
order by count(movie) desc
And if you want movies that match all the criteria, you might use:
SELECT movie
FROM genre_rel
WHERE genre IN (1, 8, 3)
GROUP BY movie
HAVING count(movie) = 3
UPDATE:
This is the best I can do in MySql. You cannot use IN because you cannot extract information about order of filters. If you add derived table as a means of filtering, you can append this information and use it to show results by positional matches. Note that you do not provide any ordering info in genre_rel table so you don't really know the importance of genres per movie. This query will give you matching movies by descending order of importance of genres in criteria:
SELECT movie
FROM genre_rel
INNER join
(
select 1 genre, 1000 weight
union all
select 8, 100
union all
select 3, 10
) weights
on genre_rel.genre = weights.genre
GROUP BY movie
order by sum(weight) desc
Note that all the movies except 5 belong to all 3 genres. If you add a column to genre_rel representing order of importance you might devise some mathematics (weight - importance or something similar).