MySQL select multiple id at the same time with optional id with php

I would like to complicate this request by changing the scenario. Here is the link to the original request. Here is the link to the original request.

I have the following MySQL table called skills.

id	idUser	idSkill
1	4	1
2	8	4
3	8	9
4	13	9
5	18	2
6	22	1
7	27	2
8	32	4
9	11	2
10	32	9
10	32	7

I need to select, for example, all idUsers that have idSkill 4 and 9 at the same time (mandatory skills).

But I would like to have the possibility to search by optional idSkills (if any).

Mandatory skills are 9 and 4

Optional skill is 7

The result would be idUser 32.

I thought of this query:

SELECT id, idUser, idSkill FROM skills WHERE idSkill IN (9,4,7) GROUP BY idUser HAVING (idSkill IN (9,4))

But it clearly does not work.

Many thanks

Solution

You can do it with aggregation and RANK() window function.

This query:

SELECT idUser
FROM skills 
WHERE idSkill IN (9, 4, 7) 
GROUP BY idUser 
HAVING SUM(idSkill IN (9, 4)) = 2 -- for the 2 mandatory skills

returns all the users with at least the 2 mandatory skills 9 and 4.

If you use an ORDER BY clause with LIMIT like this:

SELECT idUser
FROM skills 
WHERE idSkill IN (9, 4, 7) 
GROUP BY idUser 
HAVING SUM(idSkill IN (9, 4)) = 2 -- for the 2 mandatory skills
ORDER BY COUNT(*) DESC
LIMIT 1

you will get from all the users with at least the 2 mandatory skills 9 and 4, only 1 user: the one with the largest number of skills (mandatory and optional).

If you want ties returned, use RANK() window function:

SELECT idUser
FROM (
  SELECT idUser, RANK() OVER (ORDER BY COUNT(*) DESC) rnk
  FROM skills 
  WHERE idSkill IN (9, 4, 7) 
  GROUP BY idUser 
  HAVING SUM(idSkill IN (9, 4)) = 2 -- for the 2 mandatory skills
) t
WHERE rnk = 1

See the demo.