CODEWITHSUNDEEP
phpmysqlgroup-bysql-rank
nusima
nusima

Reputation: 15

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

I would like to complicate this request by changing the scenario. Here is the link to the original request. Here is the link to the original request.

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
10 32 7

I need to select, for example, all idUsers that have idSkill 4 and 9 at the same time (mandatory skills).

But I would like to have the possibility to search by optional idSkills (if any).

Mandatory skills are 9 and 4

Optional skill is 7

The result would be idUser 32.

I thought of this query:

SELECT id, idUser, idSkill FROM skills WHERE idSkill IN (9,4,7) GROUP BY idUser HAVING (idSkill IN (9,4))

But it clearly does not work.

Many thanks

Upvotes: 1

Views: 797

Answers (2)

ThunderStorm
ThunderStorm

Reputation: 155

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

Upvotes: 0

forpas
forpas

Reputation: 164089

You can do it with aggregation and RANK() 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

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

If you use an ORDER BY clause with LIMIT 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

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, use RANK() 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

See the demo.

Upvotes: 1

Related Questions