I have the following MySQL table called skills.
I need to select, for example, all idUsers that have idSkill 4 and 9 at the same time.
The result would be idUser 8 and 32.
How can I create such a query with PHP and MySQL?
One simple approach uses aggregation:
SELECT idUser FROM skills WHERE idSkill IN (4, 9) GROUP BY idUser HAVING MIN(idSkill) <> MAX(idSkill);
The above query is sargable, meaning that an appropriate index can use the
idSkill column. Consider adding this index for added performance:
CREATE INDEX idx ON skills (idUser, idSkill);
Use this query for 3 items:
SELECT idUser FROM skills WHERE idSkill IN (2, 4, 9) GROUP BY idUser HAVING COUNT(DISTINCT idSkill) = 3;
Answered By – Tim Biegeleisen
This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0