1

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

forpas
  • 160,666
  • 10
  • 38
  • 76
nusima
  • 15
  • 3

2 Answers2

1

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Thanks for your solution, it works perfectly. However, the users and the mandatory and optional skills might vary in my database. Your example is perfect if the data scenario remains the same. – nusima Jul 29 '21 at 14:42
  • @nusima I don't understand what the problem is. Of course mandatory and optional skills may vary. All you have to do is change the values in the lists. – forpas Jul 29 '21 at 14:44
  • Sorry, i tried to enter a new user that met mandatory skills and optional skills and it did not show up in the selection. I will try again now. – nusima Jul 29 '21 at 14:57
  • @nusima can you reproduce it in my demo? – forpas Jul 29 '21 at 14:58
  • Here I have inserted a user. It is not displayed even if I delete the optional skill https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=efc590095d60b8cc011e7c0fac7776b1 – nusima Jul 29 '21 at 15:08
  • @nusima if you use `;` instead of `,` in the VALUES of INSERT, then the statements are ignored. Check this: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=87dc2d390efdfb3f8f978d9b1d154bfb – forpas Jul 29 '21 at 15:10
  • I am a fool. Thanks for your help. – nusima Jul 29 '21 at 15:27
0

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

ThunderStorm
  • 155
  • 1
  • 1
  • 12
  • Where are the optional skills? – forpas Jul 29 '21 at 15:30
  • Since it's optional it should not be in the query i guess. if you run the query you can see optional skill also coming. – ThunderStorm Jul 29 '21 at 15:33
  • for the user id 8 output will be 4.9 and for the user id 32 output will be 4,9,7 – ThunderStorm Jul 29 '21 at 15:35
  • Read the question again and the comments under my answer. The requirement is to return the user(s) with at least the mandatory skills and the most of the optional skills. Your code returns all the users with the mandatory skills which was the requirement of the OP's previous question (link in the question). – forpas Jul 29 '21 at 15:36
  • 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 – ThunderStorm Jul 29 '21 at 15:53
  • does it help? please do let me know then will update in answer. – ThunderStorm Jul 29 '21 at 15:54
  • Your edited code makes skill 7 mandatory although it is optional. So if user 32 does not have skill 7 would not be returned: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e4707505939a90b09e8342dcaef71376 – forpas Jul 29 '21 at 16:05
  • it should be and as per my understanding as you want to apply condition on the set where 4 and 9 exists. on that if you apply condition = 7 then it must be and. otherwise if you want 4,9 and optional value yes/no then need to use thise code 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) or t.seven=1 – ThunderStorm Jul 29 '21 at 16:09