Join Mysql tables

Question

Join Mysql tables

Asked 6 years, 4 months ago

Viewed 129 times

0

I have a table that has the times of the day
```
 SELECT * FROM horas
```

The type of the column hora is VARCHAR(5)

And a query containing the values of the hours

SELECT DATE_FORMAT( date, '%H:%i' ) HORA
                       ,TRUNCATE( MAX( humidade ),2 ) MAX_HUMIDADE
                       ,TRUNCATE( MIN( humidade ),2 ) MIN_HUMIDADE
                   FROM medicao
                  WHERE DATE(date) = '2018-07-24'
                  GROUP BY HOUR(date)

Whose result is this:

But by doing the joinof the tables only comes a tuple

 SELECT * FROM
   HORAS A
   LEFT JOIN 
   (  SELECT DATE_FORMAT( medicao.date, '%H:%i' ) HORA
       ,TRUNCATE( MAX( humidade ),2 ) MAX_HUMIDADE
       ,TRUNCATE( MIN( humidade ),2 ) MIN_HUMIDADE
   FROM medicao
   WHERE DATE(medicao.date) = '2018-07-24'
   GROUP BY HOUR(medicao.date)
 ) B ON B.HORA = A.HORA;

Whose result is:

I started that question here, if someone wanted to understand better

2

With the query that you did, the condition of taking the data from the two tables is B.HORA = A.HORA. On the table medicacao the values of horas are closed values, such as 00:00, 01:00, ... . Then with the query that you did, the table values medicacao will only be united when the hora table hora is the same. Note that the table hora, beyond closed hours, has minutes.

– Thiago Magalhães

2018/07/24 at 18:31
Truthful! Now that I realize that I have entered the wrong hours values. That’s right.

– adventistaam

2018/07/24 at 18:33
Post that solved the problem to finalize the question.

– Thiago Magalhães

2018/07/24 at 18:35
But it was you who helped me see, thank you very much! You can add the answer

– adventistaam

2018/07/24 at 18:36

1 answer

Browser other questions tagged mysql sql

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

by Thiago Magalhães • **1,912** points · Answer 1 · 2018-07-24T18:38:39+00:00

Reason for the error:

With the query that you did, the condition of taking the data from the two tables is B.HORA = A.HORA. On the table medicacao the values of horas are closed values, such as 00:00, 01:00, ... . Then with the query that you did, the table values medicacao will only be united when the hora table hora is the same. Note that the time table, in addition to the closed hours, has the minutes.