CODEWITHSUNDEEP
mysqlsqlmariadb
sgt_johnny
sgt_johnny

Reputation: 329

MySQL Error #1064 - SQL Syntax Error

I built the following Query, for MySQL, Version 5.5.37 on Maria Db

SELECT (
   Coalesce(`w`.`ID`, "") AS `w_ID`,
   Coalesce(`w`.`reportID`, "") AS `w_reportID`,
   Coalesce(`w`.`date`, "") AS `w_date`,
   Coalesce(`w`.`amount`, "") AS `w_amount`,
   Coalesce(`w`.`add25`, "") AS `w_add25`,
   Coalesce(`w`.add50, "") AS `w_add50`,
   Coalesce(`w.deplacement`, "") AS `w_deplacement`,
   Coalesce(`t.ID`, "") AS `t_id`,
   Coalesce(`t.reportID`, "") AS `t_reportID`,
   Coalesce(`t.date`, "") AS `t_date`,
   Coalesce(`t.time`, "") AS `t_time`,
   Coalesce(`t.amount`, "") AS `t_amount`,
   Coalesce(`t.unit`, "") AS `t_unit`,
   Coalesce(`t.price`, "") AS `t_price`;
FROM workhours w
LEFT JOIN trip t on w.reportID = t.reportID 
WHERE t.reportID = 13)
UNION
SELECT (
   Coalesce(`w1`.`ID`, "") AS `w_ID`,
   Coalesce(`w1`.`reportID`, "") AS `w_reportID`, 
   Coalesce(`w1`.`date`, "") AS `w_date`,
   Coalesce(`w1`.`amount`, "") AS `w_amount`,
   Coalesce(`w1`.`add25`, "") AS `w_add25`,
   Coalesce(`w1`.add50, "") AS `w_add50`,
   Coalesce(`w1.deplacement`, "") AS `w_deplacement`,
   Coalesce(`t1.ID`, "") AS `t_id`,
   Coalesce(`t1.reportID`, "") AS `t_reportID`,
   Coalesce(`t1.date`, "") AS `t_date`,
   Coalesce(`t1.time`, "") AS `t_time`,
   Coalesce(`t1.amount`, "") AS `t_amount`,
   Coalesce(`t1.unit`, "") AS `t_unit`,
   Coalesce(`t1.price`, "") AS `t_price`
FROM `workhours` w1
RIGHT JOIN `trip` t1 on `w1`.`reportID` = `t1`.`reportID` 
WHERE `t1`.`reportID` = 13)

When I run it, I get the following error code:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS `w_ID`, Coalesce(`w`.`reportID`, "") AS `w_reportID`, Coalesce(`w`.`da' at line 2

I cant see what should be wrong at this line? Does someone having an Idea to fix this

I hope i provided enough infos.

Upvotes: 0

Views: 610

Answers (2)

John Ruddell
John Ruddell

Reputation: 25842

You have incorrect syntax with select ( and you had a ; in the first query

SELECT
   Coalesce(`w`.`ID`, "") AS `w_ID`,
   Coalesce(`w`.`reportID`, "") AS `w_reportID`,
   Coalesce(`w`.`date`, "") AS `w_date`,
   Coalesce(`w`.`amount`, "") AS `w_amount`,
   Coalesce(`w`.`add25`, "") AS `w_add25`,
   Coalesce(`w`.add50, "") AS `w_add50`,
   Coalesce(`w.deplacement`, "") AS `w_deplacement`,
   Coalesce(`t.ID`, "") AS `t_id`,
   Coalesce(`t.reportID`, "") AS `t_reportID`,
   Coalesce(`t.date`, "") AS `t_date`,
   Coalesce(`t.time`, "") AS `t_time`,
   Coalesce(`t.amount`, "") AS `t_amount`,
   Coalesce(`t.unit`, "") AS `t_unit`,
   Coalesce(`t.price`, "") AS `t_price`
FROM workhours w
LEFT JOIN trip t on w.reportID = t.reportID AND w.date = t.date
WHERE t.reportID = 13
UNION
SELECT
   Coalesce(`w1`.`ID`, "") AS `w_ID`,
   Coalesce(`w1`.`reportID`, "") AS `w_reportID`, 
   Coalesce(`w1`.`date`, "") AS `w_date`,
   Coalesce(`w1`.`amount`, "") AS `w_amount`,
   Coalesce(`w1`.`add25`, "") AS `w_add25`,
   Coalesce(`w1`.add50, "") AS `w_add50`,
   Coalesce(`w1.deplacement`, "") AS `w_deplacement`,
   Coalesce(`t1.ID`, "") AS `t_id`,
   Coalesce(`t1.reportID`, "") AS `t_reportID`,
   Coalesce(`t1.date`, "") AS `t_date`,
   Coalesce(`t1.time`, "") AS `t_time`,
   Coalesce(`t1.amount`, "") AS `t_amount`,
   Coalesce(`t1.unit`, "") AS `t_unit`,
   Coalesce(`t1.price`, "") AS `t_price`
FROM `workhours` w1
RIGHT JOIN `trip` t1 on `w1`.`reportID` = `t1`.`reportID` AND w1.date = t1.date
WHERE `t1`.`reportID` = 13

This way you can add an order to the whole result set after the parenthesis

Upvotes: 2

Vulcronos
Vulcronos

Reputation: 3456

Don't put () inside the select like you have it. You don't need the ( after the select or the ) after the where clause. Remove them both. If you need them logically for some reason the ( goes before the select keyword.

Upvotes: 0

Related Questions