incubator-empire-db-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From andrew cooke <acooke....@gmail.com>
Subject Re: How to generate sub queries
Date Thu, 26 Nov 2009 11:23:41 GMT
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 <gr-hovest@atb-bremen.de>:
> 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