MySQL select multiple id at the same time with php

Solution:

One simple approach uses aggregation:

SELECT idUser
FROM skills
WHERE idSkill IN (4, 9)
GROUP BY idUser
HAVING MIN(idSkill) <> MAX(idSkill);
Copy code

The above query is sargable, meaning that an appropriate index can use theidSkill column. Consider adding this index for added performance:

CREATE INDEX idx ON skills (idUser, idSkill);
Copy code

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;
Copy code

Solution:

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
Copy code

Solution:

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 )
Copy code

The solution can be expanded by adding moreAND EXISTS () blocks ifidSkill values list contains more than 2 values.

And this query is sargable. fiddle

Solution:

You can check with the belowMYSQL Query :

SELECT DISTINCT(idUser) FROM skill WHERE idSkill IN(4,9)
Copy code

Solution:

Try this:

SELECT DISTINCT(idUser) FROM skills
WHERE idSkill = 4 and idUser IN(select idUser from skills where idSkill = 9)
Copy code