Column not found error when combining select from multiple tables with LEFT JOIN

[Adam Burnett]

Looks like there are a couple of other threads 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.

[Thomas Mueller]

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

--
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 h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.

[alexl...@gmail.com]

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

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

[Thomas Mueller]

Hi,

As this doesn't just apply to H2, I think you could ask at StackOverflow.com.

Regards,

Thomas

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.