MySQL WHERE IN Query - ORDER BY Match

Question

I'm trying to rephrase my question, cause my last one wasn't clear to everyone.

This is my Test Table

+----------+---------+-------+
|  rel_id  |  genre  | movie |
+----------+---------+-------+
|    1     |    1    |   3   |
|    2     |    8    |   3   |
|    3     |    3    |   3   |
|    4     |    2    |   5   |
|    5     |    8    |   5   |
|    6     |    3    |   5   |
|    7     |    1    |   8   |
|    8     |    8    |   8   |
|    9     |    3    |   8   |
|   10     |    5    |   9   |
|   11     |    7    |   9   |
|   12     |    9    |   9   |
|   13     |    4    |   9   |
|   14     |    12   |   9   |
|   15     |    1    |   10  |
|   16     |    8    |   10  |
|   17     |    3    |   10  |
|   18     |    5    |   10  |
|   19     |    1    |   11  |
|   20     |    2    |   11  |
|   21     |    8    |   11  |
|   22     |    5    |   11  |
|   23     |    3    |   11  |
+----------+---------+-------+

Result should be in the following order if I look for movies with genre 1, 8, 3 : Movie no. 3, 8, 10, 5, 11 (9 is out).

If it's not possible then I just want all with the exact match "1, 8, 3", in that case I just would get movie no. 3 AND 8.

Answer 1

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