empire-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rainer Döbele <doeb...@esteam.de>
Subject re: How to generate sub queries
Date Thu, 26 Nov 2009 13:38:33 GMT
Hi Gunnar,

for subqueries you have to use a DBQuery object to wrap your subquery command.

        // Define the sub query
        DBCommand subCmd = db.createCommand();
        subCmd.select(...);
        // Wrap command for subquery
        DBQuery SQ = new DBQuery(subCmd);

        // Define the main query
        DBCommand cmd = db.createCommand();
        cmd.select(..);
        cmd.select(SQ.findQueryColumn(...));
        cmd.join(..., SQ.findQueryColumn(...));

The DBQuery class does the aliasing part.

There is an example for subqueries in the advanced sample application provided with the distribution
(empire-db-example-advanced). See function querySample(...) in SampleAdvApp.java.

Hope this helps.

Regards
Rainer

P.S. What Andrew suggested can be useful for self-joins.

Gunnar Gr-Hovest wrote:
> re: How to generate sub queries
> 
> 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