Multiple MySQL conditions in a condition group /
I have three tables that are related to the following structure.
Table Category:
+------------------+----------------+------------+
| ModuleCategoryID | ModuleCategory | RequireAll |
+------------------+----------------+------------+
| 90 | Cat A | YES |
| 91 | Cat B | NO |
+------------------+----------------+------------+
ModuleCategorySkill table:
+------------------+---------+
| ModuleCategoryID | SkillID |
+------------------+---------+
| 90 | 1439 |
| 90 | 3016 |
| 91 | 1440 |
| 91 | 3016 |
+------------------+---------+
EmployeeSkill Table:
+---------+---------+
| EmpName | SkillID |
+---------+---------+
| Emp1 | 1439 |
| Emp1 | 3016 |
| Emp2 | 1440 |
| Emp2 | 3016 |
| Emp3 | 1439 |
| Emp4 | 3016 |
+---------+---------+
Desired output:
+------------------+-------+
| ModuleCategory | Count |
+------------------+-------+
| Cat A | 1 |
| Cat B | 3 |
+------------------+-------+
I am trying to group a ModuleCategoryID and get the number of employees who have tracking skills.
Usually I can run the following query to get numbers:
select mc.ModuleCategory, Count(*) as Count from ModuleCategory as mc
join ModuleCategorySkill as mcs on mc.ModuleCategoryID = mcs.ModuleCategoryID join EmployeeSkill as es on es.SkillID= mcs.SkillID
group by mc.ModuleCategoryID
However, I have a RequireAll column in the ModuleCategory table that, if set to "YES", should only count employees as 1 if they have all the skills in that category. If set to NO, it can count each line normally and increment the counter by the number of lines it groups.
I can achieve this by writing separate queries for each modulecategoryID and using Count ()> 1 (which will find me for everyone who has all the skills for ModuleCategoryID 90). If there were 3 skills, than I would have to change it to "Count" ()> 2. If there is no one who has all the specified skills, the counter should be 0.
I need a dynamic way to do this because there is a lot of data, and writing one request for each ModuleCategoryID is not the correct approach.
Also, I am using PHP, so I can loop through and create an sql string to help me with this. But I know I will run into performance issues on large tables with a lot of skills and modulecategoryIDs.
Any advice on how to achieve this is greatly appreciated.
source to share
You can do this by joining the total category counts and then using conditional aggregation:
select modulecategory,
count(case when requireall = 'yes'
then if(s = t, 1, null)
else s
end)
from (
select modulecategory,empname, requireall, count(*) s, min(q.total) t
from employeeskill e
inner join modulecategoryskill mcs
on e.skillid = mcs.skillid
inner join modulecategory mc
on mcs.modulecategoryid = mc.modulecategoryid
inner join (
select modulecategoryid, count(*) total
from modulecategoryskill
group by modulecategoryid
) q
on mc.modulecategoryid = q.modulecategoryid
group by modulecategory, empname
) qq
group by modulecategory;
This works under the assumption that the worker will not be allocated the same skill twice, if this is what could happen, this request could be modified to support it, but this seems like a broken scenario to me.
What we have here is an internal query that matches all the information we need (category name, employee name, whether all skills are required, how many skills are in the group per employee and how many are there in the total group) with an external query. which uses a conditional score to change how rows are constructed based on value requireall
.
source to share