0

I'm looking for some help with a problem I'm having. I have done a majority of the legwork but I just can't seem to get the last bit right.

I have added a SQL Fiddle example here to better demonstrate what I'm trying to do: http://sqlfiddle.com/#!9/2955fb/38

I have 3 tables, one is just a simple users table, another is a list of movies and the third is a list of which users have liked which movies.

Now I have created a query that returns only rows which match - for example, say I am user_id 1 (Dan), only return other users who have liked the same movies as me. This works fine, it returns the persons name and which movie is matched to mine - my problem is I want a third column with an overall count of how many movies I've matched on with that user.

In the example I give, me and Ash have 2 films in common, whereas me and Sam have 3. I'd like to be able to group by user_id (to remove duplicates), and then order by the amount of movies we have in common - if that makes sense?

I hope someone can help me as spent a good few hours getting this far. If I haven't explained well, please let me know and I will elaborate.

Dan Wheeler
  • 37
  • 6

2 Answers2

2

Here is the DEMO

group_concat will give you all the movies you have in common with other user seperated with comma

count will give you a number of movies in common with that user.

order by with desc part will order results putting the biggest value on top.

SELECT users.username, group_concat(movies.name), count(movies.name)
FROM user_fave_movies t1
INNER JOIN user_fave_movies t2 ON (t2.movie_id = t1.movie_id) 
INNER JOIN users ON users.user_id = t2.user_id
INNER JOIN movies ON movies.id = t1.movie_id 
WHERE t1.user_id = 1 
AND t2.user_id <> 1
group by users.username
order by count(movies.name) desc
VBoka
  • 8,995
  • 3
  • 16
  • 24
  • 1
    Yes group_concat is also new to me too, this example works but I don't need the concatenating part.... however like I said, it's new to me so every day is a learning day, thank you! – Dan Wheeler Jan 02 '20 at 10:54
  • @DanWheeler, you say: "but I don't need the concatenating part...." but in your question you said "my problem is I want a third column with an overall count of how many movies I've matched on with that user." that means you need a second column too. And what will you have in that second column? – VBoka Jan 02 '20 at 10:58
  • 1
    In fact, thinking back to my original project, this concat would be handy. Originally I just needed the count - maybe poor wording on my part. Thank you! – Dan Wheeler Jan 02 '20 at 11:02
1

If I get this, you want the following:

SELECT users.username, COUNT(movies.name) as num_common
FROM user_fave_movies t1
INNER JOIN user_fave_movies t2 ON (t2.movie_id = t1.movie_id) 
INNER JOIN users ON users.user_id = t2.user_id
INNER JOIN movies ON movies.id = t1.movie_id 
WHERE t1.user_id = 1 
AND t2.user_id <> 1
GROUP BY users.username
ORDER BY num_common DESC;

Result: http://sqlfiddle.com/#!9/2955fb/43

username    num_common
Sam         3
Ash         2

So you are very close, what I add is:

  • GROUP BY: group by user
  • COUNT: the number of movies
  • ORDER
urban
  • 5,392
  • 3
  • 19
  • 45
  • 1
    Yes this works fine... I feel a little silly that it was that simple now looking at it. Maybe I need a coffee! Thank you very much – Dan Wheeler Jan 02 '20 at 10:53