Query calculating dates with INNER JOIN

Question

Query calculating dates with INNER JOIN

Navigation

#1 by (2 votes)

-2

Save personal, Here has an example that a guy from the community posted here and I am trying to adapt my needs but an error arises:

#1248 - Every derived table must have its own alias

My query

SELECT
 date_format(TABLE_2.data, '%d/%m/%Y %H:%i:%s') as DATA_2,
 date_format(TABLE_1.data, '%d/%m/%Y %H:%i:%s') as DATA_1, 
 Anos, 
 Mes, 
 Dias,
 Horas,
 Minutos
FROM TABLE_1
INNER JOIN
(
 SELECT
 data,
 TIMESTAMPDIFF(YEAR, data, TABLE_1.data) as Anos,
 TIMESTAMPDIFF(MONTH, data + INTERVAL TIMESTAMPDIFF(YEAR,  data, TABLE_1.data) YEAR , TABLE_1.data) AS Mes,
 TIMESTAMPDIFF(DAY, data + INTERVAL TIMESTAMPDIFF(MONTH,  data, TABLE_1.data) MONTH , TABLE_1.data) AS Dias,
 TIMESTAMPDIFF(HOUR, data + INTERVAL TIMESTAMPDIFF(DAY,  data, TABLE_1.data) DAY , TABLE_1.data) AS Horas,
 TIMESTAMPDIFF(MINUTE, data + INTERVAL TIMESTAMPDIFF(HOUR,  data, TABLE_1.data) HOUR , TABLE_1.data) AS Minutos
 FROM TABLE_2
)
ON TABLE_2.id = TABLE_1.id

I need to do the same as the example only using 2 tables and with sum of hours and minutes.

Thank you in advance for your attention

mysql mysqli

asked by anonymous 19.11.2014 / 19:12

1 answer

Sudoku does not print screen responses Good practices to keep code clean

score 2 · Accepted Answer

You need a alias in select internal. Either way, your query is wrong. TABLE_1.data does not make sense within the select internal. Try something like

SELECT
 date_format(TABLE_2.data, '%d/%m/%Y %H:%i:%s') as DATA_2,
 date_format(TABLE_1.data, '%d/%m/%Y %H:%i:%s') as DATA_1, 
 TIMESTAMPDIFF(YEAR, data, TABLE_1.data) as Anos,
 TIMESTAMPDIFF(MONTH, data + INTERVAL TIMESTAMPDIFF(YEAR,  data, TABLE_1.data) YEAR , TABLE_1.data) AS Mes,
 TIMESTAMPDIFF(DAY, data + INTERVAL TIMESTAMPDIFF(MONTH,  data, TABLE_1.data) MONTH , TABLE_1.data) AS Dias,
 TIMESTAMPDIFF(HOUR, data + INTERVAL TIMESTAMPDIFF(DAY,  data, TABLE_1.data) DAY , TABLE_1.data) AS Horas,
 TIMESTAMPDIFF(MINUTE, data + INTERVAL TIMESTAMPDIFF(HOUR,  data, TABLE_1.data) HOUR , TABLE_1.data) AS Minutos
FROM TABLE_1
INNER JOIN TABLE_2
ON TABLE_2.id = TABLE_1.id;