CODEWITHSUNDEEP
mysqlsqlgroup-bycountsql-order-by
Dan Wheeler
Dan Wheeler

Reputation: 87

SQL - Finding Users with Similar Interests

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.

Upvotes: 0

Views: 613

Answers (2)

VBoka
VBoka

Reputation: 9083

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

Upvotes: 2

urban
urban

Reputation: 5702

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

Upvotes: 1

Related Questions