Hi,
> So how is the statement to be written to succeed?
SELECT m1.X + m2.X AS X, D1.id AS d1, D2.id AS d2
FROM D AS D1 CROSS JOIN D AS D2
LEFT OUTER JOIN M_1 m1 ON m1.d1=D1.id AND m1.d2=D2.id
LEFT OUTER JOIN M_2 m2 ON m2.d1=D1.id AND m2.d2=D2.id;
The problem with your queries is the combination of 'old style' join
(with ',') and 'new style' join ('left outer join').
SELECT m1.X + m2.X AS X, D1.id AS d1, D2.id AS d2
FROM D AS D1, D AS D2
LEFT OUTER JOIN M_1 m1 ON m1.d1=D1.id AND m1.d2=D2.id
LEFT OUTER JOIN M_2 m2 ON m2.d1=D1.id AND m2.d2=D2.id;
This doesn't work on most databases:
PostgreSQL: ERROR: invalid reference to FROM-clause entry for table "d1" 42P01/0
MySQL: Unknown column 'D1.id' in 'on clause'
Derby: An ON clause associated with a JOIN operator is not valid
SELECT m1.X + m2.X AS X, D1.id AS d1, D2.id AS d2
FROM D AS D1, D AS D2
LEFT OUTER JOIN M_1 m1 ON m1.d1=d1 AND m1.d2=d2
LEFT OUTER JOIN M_2 m2 ON m2.d1=d1 AND m2.d2=d2;
The same.
PostgreSQL: ERROR: column reference "d1" is ambiguous
MySQL: Column 'd1' in on clause is ambiguous
Derby: Column name 'D1' is in more than one table in the FROM list.
Regards,
Thomas
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
