dotNET dotNET - 6 months ago 9
SQL Question

Joining two subqueries throwing error

What's wrong with the following query?

SELECT * FROM
(SELECT DATE_FORMAT(Start, '%d-%M-%Y') AS Date1,
SUM(TIMESTAMPDIFF(SECOND, Start, End)) / 3600 AS Hours
FROM timeslot WHERE UserID = 1
GROUP BY Date1) AS A

LEFT OUTER JOIN

(SELECT DATE_FORMAT(TakenAt, '%d-%M-%Y') AS Date2
FROM snapshot WHERE Deleted = 1
GROUP BY Date2) AS B

ON A.Date1 = B.Date2


It doesn't run. VS changes the query slightly (changing
GROUP BY Date1
and
GROUP BY Date2
to
GROUP BY A.Date1
and
GROUP BY B.Date2
respectively) and tells me about unknown column A.Date1 in the group statement.

Answer

I believe this is happening because of MySQL reserved words , try using back ticks ` :

SELECT * FROM 
  (SELECT DATE_FORMAT(`Start`, '%d-%M-%Y') AS Date1, 
   SUM(TIMESTAMPDIFF(SECOND, `Start`, `End`)) / 3600 AS Hours
   FROM timeslot WHERE UserID = 1
   GROUP BY DATE_FORMAT(`Start`, '%d-%M-%Y')) A 
LEFT OUTER JOIN
  (SELECT DATE_FORMAT(TakenAt, '%d-%M-%Y') AS Date2
   FROM snapshot WHERE Deleted = 1
   GROUP BY DATE_FORMAT(TakenAt, '%d-%M-%Y')) B 
ON A.Date1 = B.Date2

Or just alias every thing:

SELECT * FROM 
  (SELECT DATE_FORMAT(t.Start, '%d-%M-%Y') AS Date1, 
   SUM(TIMESTAMPDIFF(SECOND, t.Start, t.End)) / 3600 AS Hours
   FROM timeslot t WHERE t.UserID = 1
   GROUP BY DATE_FORMAT(t.Start, '%d-%M-%Y') ) A 
LEFT OUTER JOIN
  (SELECT DATE_FORMAT(s.TakenAt, '%d-%M-%Y') AS Date2
   FROM snapshot s WHERE s.Deleted = 1
   GROUP BY DATE_FORMAT(s.TakenAt, '%d-%M-%Y')) B 
ON A.Date1 = B.Date2