CODEWITHSUNDEEP
mysqlsyntaxunion
dean
dean

Reputation: 61

MySQL Syntax error in UNION ALL

I have a query that uses multiple UNION ALL. I'm getting a syntax error and I can't find it. I think it has to do with using multiple unions. I'm only using MySQL. Any help is appreciated.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALL(SELECT a.' at line 41

 SELECT   Sum(counter)                           AS counter,
         DATE_FORMAT(dtime,'%Y-%m-%d 00:00:00') AS dtime
FROM     (
                SELECT counter,
                       dtime
                FROM   MinutesVisible
                WHERE  dtime >= '2015:3:1 00:00:00'
                AND    dtime <= '2015:3:16  23:59:59'
                AND    dtime < NOW()
                AND    id='980371'
                AND    counter != 0
                UNION ALL 
                          (
                                 SELECT a.counter,
                                        a.dtime
                                 FROM   MinutesVisible a,
                                        calendar b
                                 WHERE  a.dtime = b.dtime
                                 AND    b.dtime >= '2015:3:1 00:00:00'
                                 AND    b.dtime <= '2015:3:16  23:59:59'
                                 AND    b.dtime < NOW()
                                 AND    id='979661')
                   UNION
                            (
                                   SELECT '0'  AS counter,
                                          b.dtime
                                   FROM   calendar b
                                   WHERE  dtime >= '2015:3:1  00:00:00'
                                   AND    dtime <= '2015:3:16  23:59:59'
                                   AND    dtime < NOW()
                                   AND    dtime NOT IN
                                          (
                                                 SELECT dtime
                                                 FROM   MinutesVisible
                                                 WHERE  dtime >= '2015:3:1 00:00:00'
                                                 AND    dtime <= '2015:3:16 23:59:59'
                                                 AND    dtime < NOW()
                                                 AND    id='979661' ))
                   ORDER BY dtime
                   AND      counter != 0
                   UNION ALL (this is line 41)
                             (
                                    SELECT a.counter,
                                           a.dtime
                                    FROM   MinutesVisible a,
                                           calendar b
                                    WHERE  a.dtime = b.dtime
                                    AND    b.dtime >= '2015:3:1 00:00:00'
                                    AND    b.dtime <= '2015:3:16  23:59:59'
                                    AND    b.dtime < NOW()
                                    AND    id='984121')
                      UNION
                               (
                                      SELECT '0' AS counter,
                                             b.dtime
                                      FROM   calendar b
                                      WHERE  dtime >= '2015:3:1  00:00:00'
                                      AND    dtime <= '2015:3:16  23:59:59'
                                      AND    dtime < NOW()
                                      AND    dtime NOT IN
                                             (
                                                    SELECT dtime
                                                    FROM   MinutesVisible where dtime >= '2015:3:1 00:00:00'
                                                    AND    dtime <= '2015:3:16 23:59:59'
                                                    AND    dtime < NOW()
                                                    AND    id='984121' ))
                      ORDER BY dtime
                      AND      counter != 0) AS MainTable
GROUP BY WEEKOFYEAR(dtime)
HAVING   SUM(counter) > 0

If I change the 'id=' to id in and include the other id number there and drop the second union all it works. I'm pretty sure it has something to do with the second union all. This statement is built dynamically so I cannot easily change to using in instead of multiple union alls.

Let me know if I'm not explaining this clearly and I'll provide more detail.

Upvotes: 1

Views: 3933

Answers (1)

Raushan Kuamr Jha
Raushan Kuamr Jha

Reputation: 463

check your statement just above the line where you've written (this IS line 41) Try to remove/update this statement and execute your query. 

ORDER BY
            dtime
        AND counter != 0

we can't use order in between the UNION & UNION ALL. Order by should be at the very last, after all UNION / UNION ALL statements

Upvotes: 4

Related Questions