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

Solution:

You can do it with aggregation andRANK() 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
Copy code

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

If you use anORDER BY clause withLIMIT 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
Copy code

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, useRANK() 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
Copy code

See the demo.

Solution:

Your answer will be a simple query.

select * from (select distinct b.idUser, (select 1 from skill a where a.idUser=b.idUser and a.idSkill=4) 'four', (select 1 from skill a where a.idUser=b.idUser and a.idSkill=9) 'nine', (select 1 from skill a where a.idUser=b.idUser and a.idSkill=7) 'seven' from skill b) t where t.four=1 and t.nine=1 and t.seven=1