There could be a manager table, but not necessary. Typically, a manager is also an employee, so the employee table would have a FK pointing to a PK in the same table. If you keep a separate managers table it could be hard to move around records and adjust FKs when a non-manager employee becomes manager.

To accomodate this "table has FK to PK within itself" scenario, you can write SQL like:

select e.*,m.* from Employee e, Employee m where

where all employees, manager or not are kept in the same table.


At 06:55 AM 9/29/2006, you wrote:
thanx al lot,

but i am guessing you meant:

select d.*, e.*, m.* from (department d left outer join employee e on left outer join manager m on


PS: i am not always a wise-guy :-)

On 9/29/06, Ali Demir <> wrote:
Yes, you can join on any column, FK need not be defined.

Get departments (include those that do not have employees or
managers) and their employees and their managers:

select d.*, e.*, m.* from (department d left outer join employee e on left outer join employee m on

will work if PK and FK are not defined. But you may want to have
indexes on your join columns for performance.


At 05:55 AM 9/29/2006, you wrote:
>Hi all,
>i am pretty new to Derby so maybe this is a stupid question, but i
>couldn't find it in the documentation:
>is there a way in Derby to combine OUTER JOINS on one table?
>For example:
>we have a MAIN table and some SUB tables (SUB1, SUB2...) which all
>contain MAIN's primary key.
>Is it possible to create a select that can see the MAIN table
>weither or not the SUB tables have records linked to MAIN?