MySQL WHERE IN Query - ORDER BY Match

Question

UPDATE QUESTION

Hello,

I am trying to rephrase my question because my last was not clear to everyone.

This is my test chart.

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

The result should be in the following order if I am looking for films with the genre 1, 8, 3: Movie No. 3, 8, 10, 5, 11 (9 is absent).

If this is not possible, then I just want everything with an exact match of "1, 8, 3", in which case I would just get the movie "no." 3 and 8.

Thank!

Answer 1

If I understand correctly, you want to sort the results by the number of matches in descending order. To do this, you can try:

SELECT movie
  FROM genre_rel 
 WHERE genre IN (1, 8, 3) 
 GROUP BY movie
 order by count(movie) desc

And if you want the films to meet all the criteria, you can use:

SELECT movie
  FROM genre_rel 
 WHERE genre IN (1, 8, 3) 
 GROUP BY movie
HAVING count(movie) = 3

UPDATE:

, MySql. IN, . , . , genre_rel, . :

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

, , 5, 3 . genre_rel, , ( - ).

Answer 2

:

SELECT distinct movie FROM genre_rel WHERE genre IN (1, 8, 3);

Answer 3

You specified in the criteria for the genre column. If movie 5 also contains genres 8 and 3. For this reason, you also get movie 5.

Answer 4

How about something like:

SELECT movie, SUM(gentre) AS count 
  FROM genre_rel 
  WHERE genre IN (1, 8, 3) 
  GROUP BY movie 
  ORDER BY count DESC

Answer 5

Try this query -

SELECT movie FROM genre_rel
  GROUP BY movie
ORDER BY
  IF(COUNT(IF(genre IN (1, 8, 3), 1, NULL)) = 3, genre, genre + 1000)

If 1000 is the order offset for movies that don't match, increase this value if you need to.

Alternatively, you can try this ORDER BY -

ORDER BY IF(COUNT(IF(genre IN (1, 8, 3), 1, NULL)) = 3, genre, MAX(genre))