title: MySQL Syntax error in UNION ALL
body: <p>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.</p>
  
  <p>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</p>
  
  <pre><code> SELECT   Sum(counter)                           AS counter,
           DATE_FORMAT(dtime,'%Y-%m-%d 00:00:00') AS dtime
  FROM     (
                  SELECT counter,
                         dtime
                  FROM   MinutesVisible
                  WHERE  dtime &gt;= '2015:3:1 00:00:00'
                  AND    dtime &lt;= '2015:3:16  23:59:59'
                  AND    dtime &lt; 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 &gt;= '2015:3:1 00:00:00'
                                   AND    b.dtime &lt;= '2015:3:16  23:59:59'
                                   AND    b.dtime &lt; NOW()
                                   AND    id='979661')
                     UNION
                              (
                                     SELECT '0'  AS counter,
                                            b.dtime
                                     FROM   calendar b
                                     WHERE  dtime &gt;= '2015:3:1  00:00:00'
                                     AND    dtime &lt;= '2015:3:16  23:59:59'
                                     AND    dtime &lt; NOW()
                                     AND    dtime NOT IN
                                            (
                                                   SELECT dtime
                                                   FROM   MinutesVisible
                                                   WHERE  dtime &gt;= '2015:3:1 00:00:00'
                                                   AND    dtime &lt;= '2015:3:16 23:59:59'
                                                   AND    dtime &lt; 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 &gt;= '2015:3:1 00:00:00'
                                      AND    b.dtime &lt;= '2015:3:16  23:59:59'
                                      AND    b.dtime &lt; NOW()
                                      AND    id='984121')
                        UNION
                                 (
                                        SELECT '0' AS counter,
                                               b.dtime
                                        FROM   calendar b
                                        WHERE  dtime &gt;= '2015:3:1  00:00:00'
                                        AND    dtime &lt;= '2015:3:16  23:59:59'
                                        AND    dtime &lt; NOW()
                                        AND    dtime NOT IN
                                               (
                                                      SELECT dtime
                                                      FROM   MinutesVisible where dtime &gt;= '2015:3:1 00:00:00'
                                                      AND    dtime &lt;= '2015:3:16 23:59:59'
                                                      AND    dtime &lt; NOW()
                                                      AND    id='984121' ))
                        ORDER BY dtime
                        AND      counter != 0) AS MainTable
  GROUP BY WEEKOFYEAR(dtime)
  HAVING   SUM(counter) &gt; 0
  </code></pre>
  
  <p>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.  </p>
  
  <p>Let me know if I'm not explaining this clearly and I'll provide more detail.</p>