Select multiple tables by arranging by date

Navigation
1
mysql> SELECT * FROM COLABORADOR;
+----+----------+
| ID | NOME     |
+----+----------+
|  2 | FULANO 2 |
|  3 | FULANO 3 |
+----+----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM JORNADA;
+----+---------------------+---------------------+----------------+
| ID | ENTRADA             | SAIDA               | COLABORADOR_ID |
+----+---------------------+---------------------+----------------+
|  3 | 2017-10-02 10:00:00 | 2017-10-02 19:00:00 |              2 |
|  4 | 2017-10-03 10:00:00 | 2017-10-03 19:00:00 |              2 |
|  5 | 2017-10-04 10:00:00 | 2017-10-04 19:00:00 |              2 |
|  6 | 2017-10-05 10:00:00 | 2017-10-05 19:00:00 |              2 |
|  7 | 2017-10-06 10:00:00 | 2017-10-06 19:00:00 |              2 |
|  8 | 2017-10-02 09:00:00 | 2017-10-02 18:00:00 |              3 |
|  9 | 2017-10-03 09:00:00 | 2017-10-03 18:00:00 |              3 |
| 10 | 2017-10-04 09:00:00 | 2017-10-04 18:00:00 |              3 |
| 11 | 2017-10-05 09:00:00 | 2017-10-05 18:00:00 |              3 |
| 12 | 2017-10-06 09:00:00 | 2017-10-06 18:00:00 |              3 |
+----+---------------------+---------------------+----------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM INTERVALO;
+----+---------------------+---------------------+----------------+
| ID | ENTRADA             | SAIDA               | COLABORADOR_ID |
+----+---------------------+---------------------+----------------+
| 11 | 2017-10-02 12:00:00 | 2017-10-02 13:00:00 |              3 |
| 12 | 2017-10-03 12:00:00 | 2017-10-03 13:00:00 |              3 |
| 13 | 2017-10-04 12:00:00 | 2017-10-04 13:00:00 |              3 |
| 14 | 2017-10-05 12:00:00 | 2017-10-05 13:00:00 |              3 |
| 15 | 2017-10-06 12:00:00 | 2017-10-06 13:00:00 |              3 |
| 16 | 2017-10-02 12:00:00 | 2017-10-02 13:00:00 |              2 |
| 17 | 2017-10-03 12:00:00 | 2017-10-03 13:00:00 |              2 |
| 18 | 2017-10-04 12:00:00 | 2017-10-04 13:00:00 |              2 |
| 19 | 2017-10-05 12:00:00 | 2017-10-05 13:00:00 |              2 |
| 20 | 2017-10-06 12:00:00 | 2017-10-06 13:00:00 |              2 |
+----+---------------------+---------------------+----------------+
10 rows in set (0.00 sec)

mysql> SELECT
    -> NOME,
    -> INTERVALO.ENTRADA AS INTERVALO_ENTRADA,
    -> INTERVALO.SAIDA AS INTERVALO_SAIDA
    -> FROM INTERVALO
    -> JOIN COLABORADOR
    -> ON COLABORADOR_ID = COLABORADOR.ID;
+----------+---------------------+---------------------+
| NOME     | INTERVALO_ENTRADA   | INTERVALO_SAIDA     |
+----------+---------------------+---------------------+
| FULANO 2 | 2017-10-02 12:00:00 | 2017-10-02 13:00:00 |
| FULANO 2 | 2017-10-03 12:00:00 | 2017-10-03 13:00:00 |
| FULANO 2 | 2017-10-04 12:00:00 | 2017-10-04 13:00:00 |
| FULANO 2 | 2017-10-05 12:00:00 | 2017-10-05 13:00:00 |
| FULANO 2 | 2017-10-06 12:00:00 | 2017-10-06 13:00:00 |
| FULANO 3 | 2017-10-02 12:00:00 | 2017-10-02 13:00:00 |
| FULANO 3 | 2017-10-03 12:00:00 | 2017-10-03 13:00:00 |
| FULANO 3 | 2017-10-04 12:00:00 | 2017-10-04 13:00:00 |
| FULANO 3 | 2017-10-05 12:00:00 | 2017-10-05 13:00:00 |
| FULANO 3 | 2017-10-06 12:00:00 | 2017-10-06 13:00:00 |
+----------+---------------------+---------------------+
10 rows in set (0.00 sec)

mysql> SELECT
    -> NOME,
    -> JORNADA.ENTRADA AS JORNADA_ENTRADA,
    -> JORNADA.SAIDA AS JORNADA_SAIDA
    -> FROM JORNADA
    -> JOIN COLABORADOR
    -> ON COLABORADOR_ID = COLABORADOR.ID;
+----------+---------------------+---------------------+
| NOME     | JORNADA_ENTRADA     | JORNADA_SAIDA       |
+----------+---------------------+---------------------+
| FULANO 2 | 2017-10-02 10:00:00 | 2017-10-02 19:00:00 |
| FULANO 2 | 2017-10-03 10:00:00 | 2017-10-03 19:00:00 |
| FULANO 2 | 2017-10-04 10:00:00 | 2017-10-04 19:00:00 |
| FULANO 2 | 2017-10-05 10:00:00 | 2017-10-05 19:00:00 |
| FULANO 2 | 2017-10-06 10:00:00 | 2017-10-06 19:00:00 |
| FULANO 3 | 2017-10-02 09:00:00 | 2017-10-02 18:00:00 |
| FULANO 3 | 2017-10-03 09:00:00 | 2017-10-03 18:00:00 |
| FULANO 3 | 2017-10-04 09:00:00 | 2017-10-04 18:00:00 |
| FULANO 3 | 2017-10-05 09:00:00 | 2017-10-05 18:00:00 |
| FULANO 3 | 2017-10-06 09:00:00 | 2017-10-06 18:00:00 |
+----------+---------------------+---------------------+
10 rows in set (0.00 sec)

How to do a select merge while keeping the day in line. Example 

+----------+---------------------+---------------------+---------------------+---------------------+
| NOME     | JORNADA_ENTRADA     | INTERVALO_ENTRADA   | INTERVALO_SAIDA     | JORNADA_ENTRADA     |
+----------+---------------------+---------------------+---------------------+---------------------+
| FULANO 2 | 2017-10-02 10:00:00 | 2017-10-02 12:00:00 | 2017-10-02 13:00:00 | 2017-10-02 19:00:00 |
| FULANO 2 | 2017-10-03 10:00:00 | 2017-10-03 12:00:00 | 2017-10-03 13:00:00 | 2017-10-03 19:00:00 |
| FULANO 2 | 2017-10-04 10:00:00 | 2017-10-04 12:00:00 | 2017-10-04 13:00:00 | 2017-10-04 19:00:00 |
| FULANO 2 | 2017-10-05 10:00:00 | 2017-10-05 12:00:00 | 2017-10-05 13:00:00 | 2017-10-05 19:00:00 |
| FULANO 2 | 2017-10-06 10:00:00 | 2017-10-06 12:00:00 | 2017-10-06 13:00:00 | 2017-10-06 19:00:00 |
| FULANO 3 | 2017-10-02 09:00:00 | 2017-10-02 12:00:00 | 2017-10-02 13:00:00 | 2017-10-02 18:00:00 |
| FULANO 3 | 2017-10-03 09:00:00 | 2017-10-03 12:00:00 | 2017-10-03 13:00:00 | 2017-10-03 18:00:00 |
| FULANO 3 | 2017-10-04 09:00:00 | 2017-10-04 12:00:00 | 2017-10-04 13:00:00 | 2017-10-04 18:00:00 |
| FULANO 3 | 2017-10-05 09:00:00 | 2017-10-05 12:00:00 | 2017-10-05 13:00:00 | 2017-10-05 18:00:00 |
| FULANO 3 | 2017-10-06 09:00:00 | 2017-10-06 12:00:00 | 2017-10-06 13:00:00 | 2017-10-06 18:00:00 |
+----------+---------------------+---------------------+---------------------+---------------------+
    
asked by anonymous 15.10.2017 / 23:29

2 answers

2

Try to use this; 

SELECT
 T0.NOME,
 T1.ENTRADA AS JORNADA_ENTRADA,
 T2.ENTRADA AS INTERVALO_ENTRADA,
 T2.SAIDA AS INTERVALO_SAIDA,
 T1.SAIDA AS JORNADA_SAIDA
FROM COLABORADOR AS T0
 INNER JOIN JORNADA AS T1 
 ON T1.COLABORADOR_ID = T0.ID
 INNER JOIN INTERVALO AS T2 
 ON T2.COLABORADOR_ID = T0.ID;
    
16.10.2017 / 02:19
0

I added a column to the CONFIGURATION table by putting it as a foreign key to the id of the INTERVAL table. 

mysql> SELECT * FROM JORNADA;
+----+---------------------+---------------------+----------------+--------------+
| ID | ENTRADA             | SAIDA               | COLABORADOR_ID | INTERVALO_ID |
+----+---------------------+---------------------+----------------+--------------+
|  3 | 2017-10-02 10:00:00 | 2017-10-02 19:00:00 |              2 |           11 |
|  4 | 2017-10-03 10:00:00 | 2017-10-03 19:00:00 |              2 |           12 |
|  5 | 2017-10-04 10:00:00 | 2017-10-04 19:00:00 |              2 |           13 |
|  6 | 2017-10-05 10:00:00 | 2017-10-05 19:00:00 |              2 |           14 |
|  7 | 2017-10-06 10:00:00 | 2017-10-06 19:00:00 |              2 |           15 |
|  8 | 2017-10-02 09:00:00 | 2017-10-02 18:00:00 |              3 |           16 |
|  9 | 2017-10-03 09:00:00 | 2017-10-03 18:00:00 |              3 |           17 |
| 10 | 2017-10-04 09:00:00 | 2017-10-04 18:00:00 |              3 |           18 |
| 11 | 2017-10-05 09:00:00 | 2017-10-05 18:00:00 |              3 |           19 |
| 12 | 2017-10-06 09:00:00 | 2017-10-06 18:00:00 |              3 |           20 |
+----+---------------------+---------------------+----------------+--------------+
10 rows in set (0.00 sec)

Then in select I added the two JOINs following the example of friend Anderson. 

mysql> SELECT
    -> T2.NOME, T0.ENTRADA, T1.ENTRADA, T1.SAIDA, T0.SAIDA
    -> FROM JORNADA AS T0
    -> INNER JOIN INTERVALO AS T1
    -> ON T0.INTERVALO_ID = T1.ID
    -> INNER JOIN COLABORADOR AS T2
    -> ON T0.COLABORADOR_ID = T2.ID;
+----------+---------------------+---------------------+---------------------+---------------------+
| NOME     | ENTRADA             | ENTRADA             | SAIDA               | SAIDA               |
+----------+---------------------+---------------------+---------------------+---------------------+
| FULANO 2 | 2017-10-02 10:00:00 | 2017-10-02 12:00:00 | 2017-10-02 13:00:00 | 2017-10-02 19:00:00 |
| FULANO 2 | 2017-10-03 10:00:00 | 2017-10-03 12:00:00 | 2017-10-03 13:00:00 | 2017-10-03 19:00:00 |
| FULANO 2 | 2017-10-04 10:00:00 | 2017-10-04 12:00:00 | 2017-10-04 13:00:00 | 2017-10-04 19:00:00 |
| FULANO 2 | 2017-10-05 10:00:00 | 2017-10-05 12:00:00 | 2017-10-05 13:00:00 | 2017-10-05 19:00:00 |
| FULANO 2 | 2017-10-06 10:00:00 | 2017-10-06 12:00:00 | 2017-10-06 13:00:00 | 2017-10-06 19:00:00 |
| FULANO 3 | 2017-10-02 09:00:00 | 2017-10-02 12:00:00 | 2017-10-02 13:00:00 | 2017-10-02 18:00:00 |
| FULANO 3 | 2017-10-03 09:00:00 | 2017-10-03 12:00:00 | 2017-10-03 13:00:00 | 2017-10-03 18:00:00 |
| FULANO 3 | 2017-10-04 09:00:00 | 2017-10-04 12:00:00 | 2017-10-04 13:00:00 | 2017-10-04 18:00:00 |
| FULANO 3 | 2017-10-05 09:00:00 | 2017-10-05 12:00:00 | 2017-10-05 13:00:00 | 2017-10-05 18:00:00 |
| FULANO 3 | 2017-10-06 09:00:00 | 2017-10-06 12:00:00 | 2017-10-06 13:00:00 | 2017-10-06 18:00:00 |
+----------+---------------------+---------------------+---------------------+---------------------+
10 rows in set (0.00 sec)

and it worked fine;)

    
16.10.2017 / 03:22
Turn on led with arduino using strings How do I vector product by matrix and between matrices?