OUTER JOIN on more than one table

[Guy <gu...@gmail.com>]

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?

Thanx

Re: OUTER JOIN on more than one table

[Ali Demir <de...@yahoo.com>]

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 e.mgr_id=m.id

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

Regards,
Ali


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
>e.dept_id=d.id) left outer join manager m on e.mgr_id=m.id
>
>Guy
>
>PS: i am not always a wise-guy :-)
>
>On 9/29/06, Ali Demir <<m...@yahoo.com> 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
>e.dept_id=d.id) left outer join employee m on e.mgr_id=m.id
>
>will work if PK and FK are not defined. But you may want to have
>indexes on your join columns for performance.
>
>Regards,
>Ali
>
>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?
> >
> >Thanx
>
>

Re: OUTER JOIN on more than one table

[Guy <gu...@gmail.com>]

thanx al lot,

but i am guessing you meant:

select d.*, e.*, m.* from (department d left outer join employee e on
e.dept_id=d.id) left outer join manager m on e.mgr_id=m.id

Guy

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

On 9/29/06, Ali Demir <de...@yahoo.com> 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
> e.dept_id=d.id) left outer join employee m on e.mgr_id=m.id
>
> will work if PK and FK are not defined. But you may want to have
> indexes on your join columns for performance.
>
> Regards,
> Ali
>
> 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?
> >
> >Thanx
>
>
>

Re: OUTER JOIN on more than one table

[Ali Demir <de...@yahoo.com>]

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 
e.dept_id=d.id) left outer join employee m on e.mgr_id=m.id

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

Regards,
Ali

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?
>
>Thanx