0

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.

Improve this question
5
  • 1
    In addition to the two answer: you also have a ; in the middle of the first select statement (right before the first from) Commented Aug 26, 2014 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. Commented Aug 26, 2014 at 19:34
  • @GordonLinoff see this question for more data related to the question stackoverflow.com/a/25510443/2733506 Commented Aug 26, 2014 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. Commented Aug 26, 2014 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. Commented Aug 26, 2014 at 21:06

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

Wrapping the select in (..) is totally useless. Plus there is a ; right before the first from
@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
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: sqlfiddle.com/#!2/ac273/1
@a_horse_with_no_name very cool. i just learned a new thing :) thnx i'll remove from the answer
@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
|
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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.