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.

doesn't your query work? It first looks for all movies with one of the genres and then groups by the movie. — flo, Mar 21 '12 at 08:34
It seems to work as expected, movie 5 has both the genre 8 and 3 but not 1. Do you mean you want to get movies that have all of these genres? — Matthew Riches, Mar 21 '12 at 08:36
@flo Yeah thats correct, because after i get the exact match order i need to output the movies. And i just need THE movie and not 3 times the same. Hope you understand :D — richie, Mar 21 '12 at 08:37
@Matthew Riches I want to get first movie 3 and 8, because they got the correct genre match of genre 1, 8, 3 and the last result should be movie no. 5 because here is just the first genre not the same, but the second and third got the right match. Understand? :) — richie, Mar 21 '12 at 08:40
Ok i've posted another example on [pastebin](http://pastebin.com/rBRzfkfm) 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 — richie, Mar 21 '12 at 09:41

Nikola Markovinović · Accepted Answer · 2012-03-21T11:55:27.787

12

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

edited Mar 21 '12 at 11:55

answered Mar 21 '12 at 08:41

Nikola Markovinović

18,963
5
46
51

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 Mar 21 '12 at 08:51
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. – Nikola Markovinović Mar 21 '12 at 08:55
I think you understand me :) Because the second query youve posted is doing exactl what i want! :) Thank you soo much – richie Mar 21 '12 at 09:02
Hmm the first results were ok, but the following not. I've posted another example on [pastebin](http://pastebin.com/rBRzfkfm) 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 – richie Mar 21 '12 at 09:43
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? – Nikola Markovinović Mar 21 '12 at 09:57
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. – richie Mar 21 '12 at 10:02
1

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. – Nikola Markovinović Mar 21 '12 at 10:10
@richie87 you should rephrase your question with the help of the comments here, especially the last one, which is the main requirement. – cctan Mar 21 '12 at 10:24

score 4 · Answer 2 · answered Mar 21 '12 at 08:36

4

You can do do this by following way:

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

answered Mar 21 '12 at 08:36

score 0 · Answer 3 · answered Mar 21 '12 at 08:36

0

You had given In criteria on genre column. If you movie 5 also contain genre 8 & 3 also. Because of that reason, you are getting movie 5 as well.

answered Mar 21 '12 at 08:36

kandarp

4,979
11
34
43

Yes i want to have movie no. 5, but the order of movies should be 3, 8, 5 and not 3, 5, 8 – richie Mar 21 '12 at 08:41

score 0 · Answer 4 · answered Mar 21 '12 at 08:41

0

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

answered Mar 21 '12 at 08:41

Tomasz Machura

64
4

SUM doesn't make sense here because genres are just ids. You should use COUNT(). – flo Mar 21 '12 at 08:46
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 – richie Mar 21 '12 at 08:51
You're right, of course COUNT should be used here instead of SUM! – Tomasz Machura Mar 21 '12 at 09:08

score 0 · Answer 5 · answered Mar 21 '12 at 08:45

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)

Where 1000 is a order offset for movies that don't match, increase this value if you need.

Also, you may try this ORDER BY -

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

MySQL WHERE IN Query - ORDER BY Match

5 Answers5

Linked