Select to bring the amount of certain records in related tables

Question

Speak guys, is the following, in the MYSQL database I have these 3 tables:

CONTROLS: Here I do the training entries performed. Note that it is possible to insert several employees in the same line

COLLABORATORS:

SECTORS:

The column collaborators of the CONTROL table is a foreign key of the COLLABORATORS table. The column sectors of the COLLABORATORS table is a foreign key of the SECTORS table.

I would like a select that brings me the amount of employees per sector who participated in the training.

Would look like this:

Answer 1

You can easily get this result using group by, see this example:

Select s.nome, count(s.id) as quantidade from setores s, colaboradores c where s.id = c.SETOR Group by s.id

See the example working on SQL Findle

Documentation about Group BY

Answer 2

Friend, as you are probably using the JSON data type due to the contents of the CONTRIBUTORS column in the CONTROLS table. You can do it this way.. (Note that I have not tested this select, but this is the way)

SELECT 
setor.nome as setor,
count(colaborador.id) as quantidade
FROM SETORES setor
JOIN COLABORADORES colaborador 
    ON colaborador.setor = setor.id
JOIN CONTROLES controle 
    ON JSON_CONTAINS(controle.colaboradores, CAST(colaborador.id as CHAR))
GROUP BY setor.id

Since you are working with JSON, it is worth looking at some useful functions in the documentation.

Functions That Search JSON Values

Answer 3

The most complete and secure solution I could find was the following:

declare @aux_col varchar(255)
SELECT @aux_col = colaboradores FROM controles WHERE id = 1 --id do treinamento selecionado

-- esses três passos são necessários de acordo com os caracteres especiais mostrados no exemplo
select @aux_col = replace(@aux_col, '[', '')
select @aux_col = replace(@aux_col, ']', '')
select @aux_col = replace(@aux_col, '"', '')
-- esse passo é para facilitar a pesquisa, onde será sempre consultada a string ,codigo,
select @aux_col = ',' + @aux_col + ','

SELECT s.nome as setor, count(*) as quantidade
FROM colaboradores c
JOIN setores s on s.id = c.setor
WHERE LOCATE(',' + c.id + ',', @aux_col) > 0
GROUP BY s.nome
ORDER BY s.nome

The validation in locate using comma before and after the id is necessary because if it did not exist, searching by code 1 would return truth if there was code 11, for example.