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.

Solution

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

Comments

[richie]

Yes i want to have movie no. 5, but the order of movies should be 3, 8, 5 and not 3, 5, 8

[flo]

SUM doesn't make sense here because genres are just ids. You should use COUNT().

[richie]

At first thanks for your help :) This query nearly gives me the results i wanted. But it also return movie with genres in this order 1, 8, 4, 3 - But those movies should be returned as last results

[richie]

Thanks for your help Tomasz.This query nearly gives me the results i wanted. But it also return movie with genres in this order 1, 8, 4, 3 - But those movies should be returned as last results

[Nikola Markovinović]

I don't understand. You got 3 movies and 3 genres, where are this four numbers coming from? This query returns exactly 3, 8, 5 - i have tested it before posting as answer.

[richie]

I think you understand me :) Because the second query youve posted is doing exactl what i want! :) Thank you soo much

[Tomasz Machura]

You're right, of course COUNT should be used here instead of SUM!

[richie]

Hmm the first results were ok, but the following not. I've posted another example on pastebin Please keep in mind, I'm using 3 Genres just as an example. Its possible that the query can have 5 Genres in the WHERE clause

[Nikola Markovinović]

Still in dark here. I took a look at your data. Why you want movie 11 to appear last? It has all three genres just like 3 and 8? And are you interested in getting ORDER by number of matches correct or a list of movies hzaving all listed genres?

[richie]

Movie 11 should appear last, because it got genre 1, 8, 3 but NOT exactly in this order. Maybe its easier to get ONLY the movies with 1, 8, 3 (Exactly in this order). Is that possible, because its really hard to explain my problem.

[Nikola Markovinović]

Ah, you want movies in the order they are listed in IN ()! I'm afraid not, but i'm not proficient in MySql so you might try your luck with new, better phrased question.

[cctan]

@richie87 you should rephrase your question with the help of the comments here, especially the last one, which is the main requirement.