Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 37030 invoked from network); 30 Sep 2006 03:43:54 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 30 Sep 2006 03:43:54 -0000 Received: (qmail 36145 invoked by uid 500); 30 Sep 2006 03:43:53 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 36117 invoked by uid 500); 30 Sep 2006 03:43:53 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 36106 invoked by uid 99); 30 Sep 2006 03:43:53 -0000 Received: from idunn.apache.osuosl.org (HELO idunn.apache.osuosl.org) (140.211.166.84) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 29 Sep 2006 20:43:53 -0700 Authentication-Results: idunn.apache.osuosl.org header.from=demir4@yahoo.com; domainkeys=good X-ASF-Spam-Status: No, hits=4.8 required=5.0 tests=DNS_FROM_RFC_ABUSE,DNS_FROM_RFC_POST,DNS_FROM_RFC_WHOIS,HTML_MESSAGE DomainKey-Status: good X-DomainKeys: Ecelerity dk_validate implementing draft-delany-domainkeys-base-01 Received: from [68.142.206.237] ([68.142.206.237:26422] helo=smtp104.plus.mail.mud.yahoo.com) by idunn.apache.osuosl.org (ecelerity 2.1.1.8 r(12930)) with ESMTP id 20/31-23383-5F7ED154 for ; Fri, 29 Sep 2006 20:43:50 -0700 Received: (qmail 52776 invoked from network); 30 Sep 2006 03:43:47 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Received:Message-Id:X-Mailer:Date:To:From:Subject:In-Reply-To:References:Mime-Version:Content-Type; b=G55lVDHvZvG34ThweaYilObJalLJ5ItDpiO/GlqKmLpBNaL3cU4h+cd+XXl5XTqJbJRQQONCH3kcsPP5J05QKwFkFkepGARrVC99QoJjQtkIdaa5YyWPiChdf4xdjDm9t47wH0HgvW7Zohf2hHWz0TuvJyuG1+KUj1P5d7x8jgs= ; Received: from unknown (HELO atlantis.yahoo.com) (demir4@75.41.255.171 with login) by smtp104.plus.mail.mud.yahoo.com with SMTP; 30 Sep 2006 03:43:46 -0000 Message-Id: <7.0.1.0.0.20060929203939.05f2e050@yahoo.com> X-Mailer: QUALCOMM Windows Eudora Version 7.0.1.0 Date: Fri, 29 Sep 2006 20:43:45 -0700 To: "Derby Discussion" From: Ali Demir Subject: Re: OUTER JOIN on more than one table In-Reply-To: <9555f0e0609290655x77a9afb4hb9a8eb119a1097b7@mail.gmail.com > References: <9555f0e0609290555t6f271b48qae7bb6c4e4145ada@mail.gmail.com> <7.0.1.0.0.20060929055806.052f99c0@yahoo.com> <9555f0e0609290655x77a9afb4hb9a8eb119a1097b7@mail.gmail.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=====================_1317515==.ALT" X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N --=====================_1317515==.ALT Content-Type: text/plain; charset="us-ascii"; format=flowed 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 <demir4@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 > > --=====================_1317515==.ALT Content-Type: text/html; charset="us-ascii" 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 <demir4@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


--=====================_1317515==.ALT--