Complex SQL query, many of many

Question

Complex for me, since I'm new to SQL.

I have three tables: Peoples , Interests and Peoples_Interests (many-to-many), which are related as follows:

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

I need to offer proposals to peoples with peoples most similar to them, which are based on the number of similar interests. So for example :

I'm interested in baseball, soccer and volley. I have to get an offer with another user who has as many similar interests as possible. People with 3/3 cases should be what I need if they exist (if not - 2/3, etc.).

So I need a query whose output will consist of sorting by interests of Peoples similarity.

UPDATE: Db structure:

Interests
identifier
name - string

Little people
identifier
Email

Peoples_Interests
interests_id
peoples_id

Thanks.

Answer 1

Something like that.

 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 (this may or may not be clearer.)

• Choose a person’s name and the number of interests that they share.
• From the table of people
• Join the table of interests so that table
• These are only the interests of the person whom we are trying to match.
• (group of people
• and order by the number of interests that correspond.)

---

Updated without a subquery 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)