[ http://issues.apache.org/jira/browse/TORQUE-60?page=comments#action_12443437 ] Thomas Fischer commented on TORQUE-60: --------------------------------------
Can you check if the following works: criteria.addJoin(APeer.ID, BPeer.ID, Criteria.INNER_JOIN); criteria.addJoin(APeer.ID, CPeer.ID, Criteria.LEFT_JOIN); >From the Torque documentation http://db.apache.org/torque/releases/torque-3.2/runtime/reference/read-from-db.html#Inner_joins_for_qualifying <Torque docs> Also, in SQL, there are two different ways to state an inner join. The first way is a statement like SELECT BOOK.* FROM BOOK INNER JOIN AUTHOR ON BOOK.AUTHOR_ID=AUTHOR.AUTHOR_ID SQL like this is produced if the join type is stated explicitly (as above). The second way to create an inner join is SELECT BOOK.* FROM BOOK,AUTHOR WHERE BOOK.AUTHOR_ID=AUTHOR.AUTHOR_ID A SQL statement like this is created by not supplying the join type, for example in criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID); Note that both SQL statements return the same result set. It is not possible to combine both ways of creating a join in most databases, i.e. code like criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID); criteria.addJoin(BookPeer.PUBLISHER_ID, PublisherPeer.PUBLISHER_ID, Criteria.INNER_JOIN); produces an SQL error in most databases. In most circumstances, it is recommended to state t the join type explicitly. However, if the "INNER JOIN" syntax is not supported by your database, try not to state the join type explicitly. </Torque docs> > MySQL left joins may lead to "ERROR 1054: Unknown column ... in on clause" > -------------------------------------------------------------------------- > > Key: TORQUE-60 > URL: http://issues.apache.org/jira/browse/TORQUE-60 > Project: Torque > Issue Type: Bug > Components: Runtime > Affects Versions: 3.2 > Environment: MySQL 5.0.x (where x >= 15) > Reporter: Stefan Broetz > > I have three tables A, B, and C, each of them having an ID and DATA column. > Now I want to inner join A and B on their ids and left outer join A and C on > their ids. Using > Criteria criteria = new Criteria(); > criteria.addJoin(APeer.ID, BPeer.ID); > criteria.addJoin(APeer.ID, CPeer.ID, Criteria.LEFT_JOIN); > [...] > BasePeer.doSelect(criteria); > gives me the MySQL error 1054: "Unknown column 'a.ID' in 'on clause'. The > problem is the generated SQL statement: > SELECT ... FROM a, b LEFT JOIN c ON a.id = c.id WHERE a.id = b.id ... > According to the SQL:2003 standard this means that only tables B and C are > joined and a.id is neither a column in B nor in C. If you want to join tables > A and C, your SQL statement has either to look like this > SELECT ... FROM b, a LEFT JOIN c ON a.id = c.id WHERE a.id = b.id ... > (notice that I swapped a and b in the FROM clause) or like this > SELECT ... FROM (a, b) LEFT JOIN c ON a.id = c.id WHERE a.id = b.id ... > I guess the latter is what you usually want to have. So the solution might > simply be the introduction of parantheses around the FROM clause. > See also http://bugs.mysql.com/bug.php?id=13551 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
