mysql - H2 - Select of a Join of Two Tables -

trying select combination of 2 sub selects. works fine in mysql, not work in h2 liquibase instance.

in h2 fails a:

error: error executing sql. syntax error in sql statement

and prints out entire input.

i turned on h2 code formatting in intellij , shows error around named table t1. not sure how join sub selects in h2.

select name, store_code, min, max, message  (   (     select       distinct name, level, store_code           name_mapping           store_code in ('us', 'ca')   ) t1   join   (     select id 'level', 5 'min', 7 'max', 'two' 'message' level description='slow'     union select id 'level', 3 'min', 5 'max', 'two' 'message' level description='medium'     union select id 'level', 1 'min', null 'max', 'one' 'message' level description='fast'   ) t2   on t1.level = t2.level ) group name, store_code, min, max, message; 

trying insert new information new table based on join. level table has speed information based on level id (we have description since id auto-incremented). want join names speed levels contained in table name_mapping joined on level id.

---

update

this fails syntax error in h2 works great in mysql:

 select * ( (select 'a') t1 join (select 'a') t2 on t1.a=t2.a );