How to generate sub queries

[Gunnar Gr-Hovest]

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