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 <[EMAIL PROTECTED]> 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
