title: sql select similar movies based on categories match frequency
body: <p>hi there i hope you are good guys <br>
  i want to select similar movies based on movie categories <br>
  so i have this tables in my database <br></p>
  
  <pre><code>table movies :id,title,......,date
  table movie_categories : id,id_movie,id_category
  table categories : id,category
  </code></pre>
  
  <p>where category is simply : action,drama,.....<br>
  i'm little noob noob in sql queries so i hope someone can help me <br> and i've tried this so far</p>
  
  <pre><code>SELECT m.*,COUNT(mc.*) AS cat_frequency 
  FROM movies m,movie_categories mc 
  WHERE mc.id_category IN (SELECT c.id_category FROM movie_categories c WHERE id_movie = 1)
  ORDER BY cat_frequency 
  </code></pre>
  
  <p>where 1 is the id of the movie that user currently viewing<br><br>
  <b>the expected result</b> is all movie data and with categories match frequency<br>
  for exemple let's say current watched movie have categories : <b>action,fantastic,drama</b> <br>
  so the movies that have the same categories (100% : have same categories) will be first in order , and then the movies that (66.66% : only 2 categories are matched) and so on ....<br><br>
  <b>note : i can do it in php but i want to do it with sql</b></p>