Hi, If I remember correctly, you need to use table.clone() to make a new copy of the table with a different alias, then use that in the subquery.
Andrew 2009/11/26 Gunnar Gr-Hovest <[email protected]>: > Hi, > > I would like to know to which extent sub queries in the FROM part of an > SQL statement are currently supported by empire-db. I tried to create > such a sub query in the following way: > > DBCommand subQuery = db.createCommand(); > > subQuery.select(MY_TABLE.ID.as("MY_KEY"), ...); > subQuery.join(...); > subQuery.where(...); > > DBCommand mainQuery = db.createCommand(); > > mainQuery.select(subQuery.getCmdColumn(1), ...); > mainQuery.join(subQuery.getCmdColumn(0), MY_TABLE.ID); > > The resulting SQL statement is: > > SELECT ... > FROM ( SELECT ... > FROM ... > WHERE ... > ) > INNER JOIN MY_TABLE t1 ON > t1.ID = MY_KEY > > The execution of this statement on a MySQL database fails and I get a > "MySQLSyntaxErrorException: Every derived table must have its own > alias", i.e. the correct statement including the necessary alias would be: > > SELECT ... > FROM ( SELECT ... > FROM ... > WHERE ... > ) MY_ALIAS > INNER JOIN MY_TABLE t1 ON > t1.ID = MY_ALIAS.MY_KEY > > Is it possible for me to somehow add this missing alias? Or is there > another way to generate such a sub query? > > Or is this type of subquerying simply not supported yet by empire-db? > > Thanks, > Gunnar >
