Select multiple tables by sorting by date

Asked

Viewed 35 times

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 make a select by merging but keeping the same line for the day. 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 |
+----------+---------------------+---------------------+---------------------+---------------------+
mysql select

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;

  • I ended up adding a column of the JORNADA table because the way it was I would have no reference between them. I do not know if because of this your select did not take as an example. But it was valid because it was doubtful in by two Joins and based on your example helped to understand. Thanks anyway

0

I added a column to the JOURNEY table by placing it as a foreign key to the INTERVAL table id.

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 ;)

Browser other questions tagged mysql select

You are not signed in. Login or sign up in order to post.