0

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

nusima
  • 15
  • 3

5 Answers5

1

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;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • @Akina ...but this exactly answers the question which was asked. For 3 or more skills, we can check the distinct count, as one of the other answers is doing. The version I have above will outperform anything using `COUNT`, which can't use an index. – Tim Biegeleisen Jul 29 '21 at 05:51
  • @TimBiegeleisen Your query works perfectly, but if the query selects 3 idSkills (4,9,12) the result also includes users with only 2 idSkills (4,9) – nusima Jul 29 '21 at 06:08
  • @nusima Check the edit for a more generalized version. But for 2 skills only, the first part of my answer is a good way to go. – Tim Biegeleisen Jul 29 '21 at 06:10
1

you can try this query. simply select userIds and add GROUP BY to avoid repeated userIds, and if you're using more skillIds like for example (4,9,2) then change the HAVING COUNT to 3 and so on.

SELECT idUser FROM your_table
WHERE idSkill IN (4, 9)
GROUP BY idUser
HAVING COUNT(DISTINCT idSkill) = 2
Tabasum Wahab
  • 41
  • 3
0

One more solution:

SELECT DISTINCT idUser 
FROM tablename t0
WHERE EXISTS ( SELECT NULL
               FROM tablename t1
               WHERE t0.idUser = t1.idUser 
                 AND t1.idSkill = 4 )
  AND EXISTS ( SELECT NULL
               FROM tablename t2
               WHERE t0.idUser = t2.idUser 
                 AND t2.idSkill = 9 )

The solution can be expanded by adding more AND EXISTS () blocks if idSkill values list contains more than 2 values.

And this query is sargable. fiddle

Akina
  • 39,301
  • 5
  • 14
  • 25
-1

You can check with the below MYSQL Query :

SELECT DISTINCT(idUser) FROM skill WHERE idSkill IN(4,9)
Brane
  • 3,257
  • 2
  • 42
  • 53
Subir C
  • 23
  • 8
  • Please add some explanation to your answer such that others can learn from it. How does this query select only the users that are assigned to **both** skills? – Nico Haase Jul 29 '21 at 06:10
-1

Try this:

SELECT DISTINCT(idUser) FROM skills
WHERE idSkill = 4 and idUser IN(select idUser from skills where idSkill = 9)
Brane
  • 3,257
  • 2
  • 42
  • 53
CHIRAG
  • 77
  • 1
  • 3
  • 1
    Please add some explanation to your answer such that others can learn from it. How does this query select only the users that are assigned to **both** skills? – Nico Haase Jul 29 '21 at 06:10