Abid Abid - 2 months ago 5
MySQL Question

Calculating maximum using existence in pipe separated column values in MySQL

COnsider below table

Emp Table
Employee can belong to more than one department in below form

emp departments
E1 D1|D2|D3
E2 D2
E3 D1|D3


Departments Table

departments Manager
D1 M1
D2 M2


How can we find the manager with the maximum employees?

Answer

First of all, if you can, you should change the structure of your data.
The employees table should contain one emp-department relation on each row, employee with more than one should have several rows.

If you stick to the current structure, you can use join to get a table with the mappings between manager's and employees:

select D.manager, E.emp from departments as D inner join employees as E on
 E.departments like CONCAT( '%|' , D.department, '|%');

and on this table you can use count to get the number of employees per manager:

select manager, count(*) from (
  select D.manager, E.emp from departments as D inner join employees as E on
    E.departments like CONCAT( '%|' , D.department, '|%')
) as X group by X.emp;