complex SQL query, many to many

Question

Complex for me as I am newbie in SQL.

I have three tables - Peoples, Interests and Peoples_Interests (many-to-many) - that are connected in the following way:

People has many Interests through Peoples_Interests
Interest has many Peoples through Peoples_Interests

I need to propose suggestions to Peoples with most similar to them Peoples, which is based on amount of similar Interests. So for example:

I am interested in baseball, football and volley. I should get suggestion with another user that have as many similar interests as possible. People with 3/3 occurrences should be what I need if they exist (if not - 2/3 and such).

So I need a query that output will consist of sorted by interests similarity Peoples.

UPDATE: Db structure:

Interests
id
name - string

Peoples
id
email

Peoples_Interests
interests_id
peoples_id

Thank you.

Answer 1

Something like this.

Select people.id, people.name, count(interest.id)
from people
left join people_interests on people.id = people_interests.peopleid 
left join interests on people_interests.interestid = interests.interest.id
where interests.id in (select id from interests where interests.peopleid = @inputuserid)
group by people.id, people.name
order by count(interest.id)

In english (which may or may not make it clearer.)

• Select the person's name and the number of interests they share
• From the people table
• Join the interests table such that that table
• Is only the interests of the person we are trying to match.
• (group by people
• and order by the number of interests that match.)

---

Updated without the sub query but less clear

Select people.id, people.name, count(interest.id)
from people
left join people_interests on people.id = people_interests.peopleid 
left join interests on people_interests.interestid = interests.interest.id
inner join interest i2 on (interests.id = i2.id and i2.people_id = @inputuserid)
group by people.id, people.name
order by count(interest.id)