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.
