title: H2 - Select of a Join of Two Tables
body: <p>Trying to select the combination of two sub selects. This works fine in MySQL, but does not work in my H2 liquibase instance.</p>
  
  <p>In H2 it fails with a:</p>
  
  <blockquote>
    <p>Error: Error executing SQL. Syntax error in SQL statement </p>
  </blockquote>
  
  <p>And prints out the entire input.</p>
  
  <p>I turned on H2 code formatting in Intellij and it shows an error around the named table <code>t1</code>. Not sure how to do a join with sub selects in H2.</p>
  
  <pre><code>SELECT name, store_code, min, max, message
   FROM (
    (
      SELECT
        DISTINCT name, level, store_code
      FROM
        name_mapping
      WHERE
        store_code in ('us', 'ca')
    ) t1
    JOIN
    (
      SELECT id as 'level', 5 as 'min', 7 as 'max', 'two' as 'message' from level where description='slow'
      UNION SELECT id as 'level', 3 as 'min', 5 as 'max', 'two' as 'message' from level where description='medium'
      UNION SELECT id as 'level', 1 as 'min', NULL as 'max', 'one' as 'message' from level where description='fast'
    ) t2
    ON t1.level = t2.level
  )
  GROUP BY name, store_code, min, max, message;
  </code></pre>
  
  <p>Trying to insert new information into a new table based on the join. The <code>level</code> table has speed information based on level id (we have to look up by description since the id is auto-incremented). I want to join that will all the names for those speed levels which are contained in another table <code>name_mapping</code> joined on the level id. </p>
  
  <hr>
  
  <p><strong>Update</strong></p>
  
  <p>This also fails with a Syntax Error in H2 but works great in MySQL:</p>
  
  <pre><code> select * from ( (select 'a') t1 join (select 'a') t2 on t1.a=t2.a );
  </code></pre>