In the clause ON
of JOIN
you can use the operator IN
and compare with all columns containing the id
employee. As you want to know the total time per sector, it is also necessary to add a GROUP BY
by sector:
SELECT colaboradores.setor, SUM(eventos.tempo) tempoTotal
FROM colaboradores
INNER JOIN eventos
ON colaboradores.id IN (
eventos.c1, eventos.c2, eventos.c3, eventos.c4, eventos.c5,
eventos.c6, eventos.c7, eventos.c8, eventos.c9, eventos.c10
)
GROUP BY colaboradores.setor
Upshot
+----------------+
|setor|tempoTotal|
|-----|----------|
| A| 1780|
| E| 2260|
| P| 2860|
| Q| 2860|
| X| 1660|
+----------------+
See working on Sql Fiddle.
Additional Information
Store event contributors within the table eventos
is not ideal for the data normalization. This can generate several problems, one of them is that you limit the number of contributors in an event by 10, if one day there is the 11th, you will have a great rework to do.
As the relationship of eventos
and colaboradores
is N:N
it is necessary to create a third table to reference.
But in this case, as the table eventos
only contains one column, you could do the reverse, one collaborator per row and for each collaborator store time:
CREATE TABLE eventos(
id INT PRIMARY KEY AUTO_INCREMENT,
idcolaborador INT,
tempo INT,
CONSTRAINT fk_eventos_colaboradores FOREIGN KEY (idcolaborador) REFERENCES colaboradores (id)
);
Thus its SELECT
would look like this:
SELECT colaboradores.setor, SUM(eventos.tempo) tempoTotal
FROM colaboradores
INNER JOIN eventos
ON colaboradores.id = eventos.idcolaborador
GROUP BY colaboradores.setor
See working on Sql Fiddle.