Is it possible to use Inner Join in several columns of the same table in a Select?

Question

In the database there are 2 related tables.

Collaborators:

Events (here makes the input of training time by collaborator):

I need to know the total training time of each sector. Select below works, but only for column C1. I need to go through the other columns too (C2, C3, C4, C5 etc), however I have not yet figured out how to do.

SELECT SUM(eventos.tempo)
FROM colaboradores
INNER JOIN eventos
ON colaboradores.id = eventos.c1

Answer 1

You can create as many conditions as you want in Inner John

SELECT colaboradores.id, SUM(eventos.tempo) 
FROM colaboradores INNER JOIN eventos ON colaboradores.id = eventos.c1 
                                      OR colaboradores.id = eventos.c2 
                                      OR colaboradores.id = eventos.c3 
                                      OR colaboradores.id = eventos.c4 
                                      OR colaboradores.id = eventos.c5 
                                      OR colaboradores.id = eventos.c6 
                                      OR colaboradores.id = eventos.c7 
                                      OR colaboradores.id = eventos.c8 
                                      OR colaboradores.id = eventos.c9 
                                      OR colaboradores.id = eventos.c10 
GROUP BY colaboradores.id 

The group by is to do the sum per person

Answer 2

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.