Select Parent where it has ALL requested children (Not just Any of them)-CodePudding

I'm building a search tool for my application (PHP: Laravel/Lumen, MySQL), where users can have search criteria where a parent row is returned only if it has children matching ALL IDs requested.

Example Table Structure:

People Table:

id, name, etc 
-------------------------
person123, David, ...
person888, Divya, ...

Interests Table:

id, person_id, interest_id, etc
---------------------------------
1, person123, interestABC, ...
2, person123, interestXYZ, ...
3, person888, interestABC, ...

Problematic User Story

Select Person that has interestABC AND interestXYZ

I'm able to select people that have any of the requested interests, for example:

Select People that have any of the interests: interestABC, interestXYZ

SELECT people.id FROM `people` 
INNER JOIN `people_interests` on `people`.`id` = `people_interests`.`person_id` 
WHERE `department` = "something" 
    and `people_interests`.`interest_id` in ("interestABC","interestXYZ") 
GROUP BY people.id;

and I can select people have any ANY interest or NO interests.

Question

Is there a way to do this in SQL? Or will have to do something like my IN query above and filter the results in the application code?

CodePudding user response：

Use HAVING clause : https://dev.mysql.com/doc/refman/8.0/en/select.html

It should be something like :

    SELECT people.id FROM `people` 
    INNER JOIN `people_interests` on `people`.`id` = `people_interests`.`person_id` 
    WHERE `department` = "something" 
    AND `people_interests`.`interest_id` in ("interestABC","interestXYZ")
    GROUP BY people.id
    HAVING  COUNT(people.id)>=2;