MySQL Error #1064 - SQL Syntax Error

Question

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.

In addition to the two answer: you also have a `;` in the middle of the first `select` statement (right before the first `from`) — , Aug 26 '14 at 19:31
The `where` clause turns the `left join` in the first subquery into an inner join. So, I think only the second subquery is necessary. — Gordon Linoff, Aug 26 '14 at 19:34
@GordonLinoff see this question for more data related to the question http://stackoverflow.com/a/25510443/2733506 — John Ruddell, Aug 26 '14 at 19:35
@hesamesa0r . . . I offered a different way of getting a `full outer join` in MySQL. It might simplify the query that you are trying to write. — Gordon Linoff, Aug 26 '14 at 19:43
This question was caused by a simple typographical error. While similar questions may be on-topic here, this one was resolved in a manner unlikely to help future readers. — Kermit, Aug 26 '14 at 21:06

score 2 · Accepted Answer · edited Aug 26 '14 at 21:05

2

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

edited Aug 26 '14 at 21:05

Peter O.

32,158
14
82
96

answered Aug 26 '14 at 19:28

John Ruddell

25,283
6
57
86

Wrapping the `select` in `(..)` is totally useless. Plus there is a `;` right before the first `from` – Aug 26 '14 at 19:31
@a_horse_with_no_name i already answered a question from this same user.. giving this full outer select.. the OP wants to order the data by a date.. so the () are needed so you can order the whole result set – John Ruddell Aug 26 '14 at 19:32
`order by` in a `union` always sorts the *whole* union and is only allowed after the last `select` part. There is no need to put the union parts into parentheses: http://sqlfiddle.com/#!2/ac273/1 – Aug 26 '14 at 19:36
@a_horse_with_no_name very cool. i just learned a new thing :) thnx i'll remove from the answer – John Ruddell Aug 26 '14 at 19:37
@a_horse_with_no_name so I guess the only reason why you would want to use that syntax is if you are trying to order it per union'd query and then order it again.. interesting – John Ruddell Aug 26 '14 at 19:40
thanks that works... but...i don't get the result i want..but that's not part of the question.. – sgt_johnny Aug 26 '14 at 19:49
@hesamesa0r if you would like to provide a sqlfiddle with the result you are getting and the expected result I can try and help. – John Ruddell Aug 26 '14 at 19:50
@JohnRuddell this fiddle shows the actual output:http://sqlfiddle.com/#!2/729dd/1 this one shows what i want http://sqlfiddle.com/#!2/729dd/2 But with named columns, because date is double, and this is not good, it should be wdate and tdate like something. It should display all entries, if w.date and t.date match, merge them to one line, if the dont match set the not matching table fields to NULL. Basicalli i just need to change the names in the second fiddle – sgt_johnny Aug 26 '14 at 20:00
@hesamesa0r here.. you forgot to add the date on the join condition for the left join and right join.. check my original answer where I gave you the left and right joins.. you need to join on the id and the date to get it to filter out duplicates.. see here http://sqlfiddle.com/#!2/729dd/3 – John Ruddell Aug 26 '14 at 20:24
yey but, now one from w_ is missing, the one whit date 24.8.14, and the first and the last one in the fiddle are duplicates? I dont know why, it starts troubling with names, because the first fiddle i posted, works perfectly with no duplicates, maybe its easier to change the column names to tDate and wDate in the table itself? this should get rid of the double name problem i think... – sgt_johnny Aug 27 '14 at 05:00
and why is W_reportID sometimes a number and sometimes a date? thats what i wonder – sgt_johnny Aug 27 '14 at 05:05

score 0 · Answer 2 · answered Aug 26 '14 at 19:30

0

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.

answered Aug 26 '14 at 19:30

Vulcronos

3,428
3
16
24

MySQL Error #1064 - SQL Syntax Error

2 Answers2