SQL Query - How to filter based off of many similar traits-CodePudding

Would anyone know what the SQL query would look like for the following scenario?

My first table is composed of list of users such as: Table 1 Name: Users

[
{id: 1, name: Bob},
{id:2, name: Sam},
{id:3, name: John}
]

My second table is composed of list of hobbies such as: Table 2 Name: Hobbies

[
{id:1, hobby: Soccer},
{id:2, hobby: Cooking},
{id3, hobby: Reading}
]

Since a user can have many hobbies, i have a relational table to link the 2 tables above. For example, if user 1 Bob has soccer and cooking as hobby, user 2 Sam has reading as hobby, and user 3 John had cooking for hobby, the table would look like: Table 3 Name: UsersHobby

[
{id:1, user_id:1, hobby_id:1},
{id:2, user_id:1, hobby_id:2},
{id:3, user_id:2,hobby_id:3},
{id:4, user_id:3, hobby_id:2}
]

My question is, how would i go on about filtering as a specific user and see which other users in the database shares 1 or more similar hobby as me. For example, as Bob i want a table that returns [3] because user_id 3 who is John shares a same hobby as me which is cooking. User id 2 who is Sam would not show up because his hobby is reading, and Bob does not have reading as his hobby. However, if db got updated and there was a 4th user who had soccer as hobby. Then the return would be [3,4]. (User 3 is inserted because they share hobby id 2 and user 4 is inserted because they share hobby id 1)

Any help is greatly appreciated and thank you for taking the time to read this!

I am able to filter if i had an array of hobby that a specific user has and iterate through each hobby 1 by 1 to filter who has the same hobby as me. However, i was wondering if there was an easier and more effective way to do so. For example, for Bob above, his hobby array would look like [1,2]. Then iterating through the array, filter the SQL to find user whos hobby_id is also 1 and/or 2.

CodePudding user response：

like this ?

select id, name from Users 
where id in (
  select user_id from UsersHobby 
  where hobby_id in (
    select hobby_id from UsersHobby where user_id in 
    (select id from Users where name = 'Bob')
  )
)