Self joining tables

[Edward Mostrom <em...@gmail.com>]

How would you do a select in empire-db where a table joins to itself.

select p.name, c.name from person p join person c on c.parent_id = p.id
where p.status = 1
and c.status < 5

re: Self joining tables

[Rainer Döbele <do...@esteam.de>]

Dear Edward,

 

Self-joins are easy to achieve.

Empire-DB automatically assigns an alias for every instance of a table object.

So all you need is two instances of your table.

 

Here's an example:

The following code:

    SampleDB db = new SampleDB(); 

    SampleDB.Departments DEP1 = new Departments(db);

    SampleDB.Departments DEP2 = new Departments(db);

    // Create the command

    DBCommand cmd = db.createCommand();

    cmd.select(DEP1.NAME, DEP2.NAME);

    cmd.join  (DEP1.DEPARTMENT_ID, DEP2.PARENT_ID);

    String sql = cmd.getSelect();

 

will produce something like:

    SELECT t3.NAME, t4.NAME

    FROM DEPARTMENTS t3 INNER JOIN DEPARTMENTS t4 ON t4.DEPARTMENT_ID = t3.PARENT_ID

 

Regards,

Rainer

 

 

Edward Mostrom wrote:

re: Self joining tables

 

How would you do a select in empire-db where a table joins to itself.

select p.name, c.name from person p join person c on c.parent_id = p.id
where p.status = 1
and c.status < 5