CODEWITHSUNDEEP
mysql
WeekendCoder
WeekendCoder

Reputation: 1035

Sort users by similar interests

i have 2 tables like this:

users:

user_id|user_name

interests:

user_id|interest_id|interest_name

For example 'interests' is filled like this:

123|1|Football
123|2|Swimming
123|3|Skiing
456|2|Swimming
...

Now i'm (user_id 123) logged in and i would like to know who has the most common interests like me (sorted descending).

The result should be something like this:

User 1: 45 interests in common (and list them)
User 2: 23 interests in common (...)
User 3: 11 interests in common (...)

Any idea how to solve this?

I would perhaps read my interests first into an array and then do a loop or something?

Thanks!

Upvotes: 0

Views: 76

Answers (2)

xQbert
xQbert

Reputation: 35333

The way I see it you want the userID, count and list(interest_name)

So we just need to join interests to itslef on the interest_ID and then limit by "my Interests" (123) and use simple aggregation and a group_concat to get the list.

SELECT OI.User_ID
     , count(Distinct OI.Interest_ID) CommonInterestCount
     , Group_concat(OI.Interest_name) InterestList
FROM interests MyI
LEFT JOIN interests  OI
   on OI.Interest_ID = MyI.Interest_ID
WHERE MyI.user_ID = '123'
GROUP BY OI.user_ID

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

Could be you need a count(*) and group by

 select interests.user_id, interests.count(*) , users.user_name
 from  interests  
 inner join users on users.user_id = interests.user_id
 where interest_id in (select interest_id from interests where user_id = 123)
 group by interests.user_id,

Upvotes: 1

Related Questions