Hi, As this doesn't just apply to H2, I think you could ask at StackOverflow.com.
Regards, Thomas On Thu, Nov 14, 2013 at 1:18 PM, <[email protected]> wrote: > How to produce the results from this "old style" statement in a h2's "new > style"? > > SELECT (A a, B b) LEFT JOIN C c ON a.x=c.x AND b.x=c.x > > On Thursday, September 5, 2013 8:44:56 AM UTC+3, Thomas Mueller wrote: >> >> Hi, >> >> This is not a bug. You are mixing "old style" (list of tables) joins with >> "new style" (left join). Other databases such as PostgreSQL, MySQL(!), and >> Apache Derby will also throw an exception (invalid reference to FROM-clause >> entry for table "a"; Unknown column 'A.ID' in 'on clause; Column 'A.ID' >> is either not in any table in the FROM list). Use the following query >> instead: >> >> SELECT A.* FROM A >> inner join B on a.id = b.id >> LEFT JOIN C ON A.ID = C.ID; >> >> Regards, >> Thomas >> >> >> On Thursday, September 5, 2013, Adam Burnett wrote: >> >>> Looks like there are a couple of >>> other<https://groups.google.com/forum/#!searchin/h2-database/left$20join$20column/h2-database/Du_pl8IkTdg/QnUuiaPvXjEJ> >>> threads<https://groups.google.com/forum/#!searchin/h2-database/left$20join$20column/h2-database/9cnIYyzrn1g/jqNrONnhW70J>along >>> similar lines but I've found that trying to select from multiple >>> tables while also using a LEFT JOIN causes the query to fail with a column >>> not found error. Attached is a sql script to reproduce the issue. Is the >>> following query valid H2 syntax (it is for MySQL & HSQLDB)? >>> >>> SELECT >>> A.* >>> FROM >>> A, >>> B >>> LEFT JOIN C >>> ON >>> A.ID = C.ID >>> WHERE >>> A.ID = B.ID; >>> >>> ... >>> Column "A.ID" not found; SQL statement: >>> ... >>> >>> Apologies if this is a duplicate. >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "H2 Database" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> To post to this group, send email to [email protected]. >>> Visit this group at http://groups.google.com/group/h2-database. >>> For more options, visit https://groups.google.com/groups/opt_out. >>> >> -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/groups/opt_out. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
