Issue
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 |
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?
Many thanks
Solution
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);
Edit:
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