Joining two subqueries throwing error

Question

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.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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

Joining two subqueries throwing error

Sign up