Join many to many tables optionally

I have below tables

User

user_id  name
101      Tony
102      Skyle
103      Kenne

Intrest

intrest_id intrest_name
201          Eating
202          Sleeping
203          Drinking

Hobby

hobby_id  hobby_name
301         Smoking
302         Hiking
303         Browsing

User_Intrest

user_id  intrest_id
101      201
102      201
102      202
103      201
103      202
103      203

User_Hobby

user_id  hobby_id
101      301
102      301
102      302
103      301
103      302
103      303

Now to find user ids who have both interests Eating and Sleeping I have written

select u.user_id
from user u, intrest i, user_intrest ui
where u.user_id = ui.user_id 
and i.intrest_id = ui.intrest_id and i.intrest_name in ('Eating', 'Sleeping')
group by u.user_id
having count(i.intrest_name) = 2

Output

user_id
102
103

Same as above I also can find user ids with hobbies Smoking, Hiking, Browsing as below

select u.user_id
from user u, hobby h, user_hobby uh
where u.user_id = uh.user_id 
and h.hobby_id = uh.hobby_id and h.hobby_name in ('Smoking', 'Hiking', 'Browsing')
group by u.user_id
having count(i.intrest_name) = 3

Output

user_id
    103

Now I want to mix these two optionally in a way that if only interests are passed then users with those interests will be found or if only hobbies are passed then users with those hobbies are found or if both interests and hobbies are passed then users with those interests as well as hobbies are found

Update: This is part of a spring data rest api with native query where query parameters are optional. Below is a working example for finding users for intrests. Now I want to expand it to include hobbies so both will be used if both are passed in the request to find users

@RestResource(path = "getUserByIntrestsAndHobbies", rel = "getUserByIntrestsAndHobbies")
    @Query(value = "SELECT u FROM User u WHERE (COALESCE(:intrests, NULL) IS NOT NULL AND u.userId IN (SELECT u.userId " +
            "FROM User u, Intrest i, UserIntrest ui " +
            "WHERE u.userId = ui.userId AND i.intrestId = ui.intrestId " +
            "AND i.intrestName IN (:intrests) " +
            "GROUP BY u.userId " +
            "HAVING (:intrestsSize IS NULL OR :intrestsSize = count(i.intrestName))))"
    )
    Page<User> getUsersByIntrestsAndHobbies(@Param("intrests") List<String> intrests,
                                                                           @Param("intrestsSize") Long intrestsSize,
                                                                           @Param("hobbies") List<String> hobbies,
                                                                           @Param("hobbiesSize") Long hobbiesSize,
                                                                           Pageable pageable);

Answer

OK, the problem becomes a lot clearer when you use explicit joins (instead of the implicit, depreciated, comma-separated joins). You then see that you need to use left joins to allow a record to be returned when there is match.

select u.[user_id]
from [user] u
left join user_intrest ui on ui.[user_id] = u.[user_id]
left join intrest i on i.intrest_id = ui.intrest_id and i.intrest_name in ('Eating', 'Sleeping')
left join user_hobby uh on uh.[user_id] = u.[user_id]
left join hobby h on h.hobby_id = uh.hobby_id and h.hobby_name in ('Smoking', 'Hiking', 'Browsing')
group by u.[user_id]
having count(distinct i.intrest_name) = 2 and count(distinct h.hobby_name) = 3;

• Its best practice not to use reserved words such as user or user_id as you will always need to escape them.

• Aside from the id column, there is no need to prepend the table name to a column name e.g. hobby_name should just be name – you’re just giving yourself more typing as it stands.

• Interest is not spelt Intrest 🙂

• If you provide DDL/DML for the sample data then its a lot easier for us to test.