incubator-empire-db-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gunnar Gr-Hovest <gr-hov...@atb-bremen.de>
Subject How to generate sub queries
Date Thu, 26 Nov 2009 09:40:36 GMT
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

Mime
View raw message