title: mysql - cannot create view that contains union
body: <p>I have a mysql query that uses union to join multiple queries into one result set. the query works perfectly.</p>
  
  <p>When I want to use the same query to create a view I then get an error message:</p>
  
  <pre><code>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 'union select hulaminloadnumber2,deliveryid,drop1customer from localjhb where hul' at line 2
  </code></pre>
  
  <p>my query that works perfectly is:</p>
  
  <pre><code>select hulaminloadnumber1,deliveryid,drop1customer from localjhb where hulaminloadnumber1&gt;0
  union
  select hulaminloadnumber2,deliveryid,drop1customer from localjhb where hulaminloadnumber2&gt;0
  union
  select hulaminloadnumber3,deliveryid,drop1customer from localjhb where hulaminloadnumber3&gt;0
  union
  select hulaminloadnumber4,deliveryid,drop1customer from localjhb where hulaminloadnumber4&gt;0
  union
  select hulaminloadnumber5,deliveryid,drop1customer from localjhb where hulaminloadnumber5&gt;0
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/9HxEN.jpg" alt="query results"></p>
  
  <p>my query to create the view is:</p>
  
  <pre><code>create view View_LoadvsCustomer as (
  select hulaminloadnumber1,deliveryid,drop1customer from localjhb where hulaminloadnumber1&gt;0
  union
  select hulaminloadnumber2,deliveryid,drop1customer from localjhb where hulaminloadnumber2&gt;0
  union
  select hulaminloadnumber3,deliveryid,drop1customer from localjhb where hulaminloadnumber3&gt;0
  union
  select hulaminloadnumber4,deliveryid,drop1customer from localjhb where hulaminloadnumber4&gt;0
  union
  select hulaminloadnumber5,deliveryid,drop1customer from localjhb where hulaminloadnumber5&gt;0)
  </code></pre>
  
  <p>this produces the below error in PHPMyadmin:
  <img src="https://i.stack.imgur.com/ol2vv.jpg" alt="error message"></p>
  
  <p>The queries are all from a badly designed table so there should be no formatting or collation issues. Can a view be created against a union query?</p>
  
  <p>Any advice is appreciated.</p>
  
  <p>Have a GREAT weekend,
  Ryan</p>