From: | "Dmitri Colebatch" <dim(at)bigpond(dot)net(dot)au> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | join question - three tables, two with foreign keys to the first |
Date: | 2002-06-13 14:13:22 |
Message-ID: | 08af01c212e4$7a12a180$fe00a8c0@hobbes |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
hey all,
here's my situation. three tables:
create table employee (id integer primary key, name varchar(32));
create table employee_leave (id integer primary key, employee_id integer,
from_date date, to_date date, constraint emp_leave_fk foreign key
(employee_id) references employee (id));
create table employee_pay (id integer primary key, employee_id integer,
amount integer, constraint emp_pay_fk foreign key (employee_id) references
employee (id));
and some sample data:
insert into employee (id, name) values (1, 'dim');
insert into employee_leave (id, employee_id, from_date, to_date) values (2,
1, '10-05-2002', '14-05-2002');
insert into employee_leave (id, employee_id, from_date, to_date) values (1,
1, '10-06-2002', '14-06-2002');
insert into employee_pay(id, employee_id, amount) values (1, 1, 100);
insert into employee_pay(id, employee_id, amount) values (2, 1, 100);
and I want to retrieve the information for an employee (all pay, and all
leave) in one query.... here's what I've got
select emp.name, lv.from_date, lv.to_date, pay.amount
from employee as emp
left outer join employee_leave as lv on emp.id = lv.employee_id
left outer join employee_pay as pay on emp.id = pay.employee_id
where emp.id = 1
problem is that I dont get the null values I expect.... I want to be able to
iterate through the resultset and determine if the record is from the leave
table or pay table - but because I dont get null results, I cant....
any pointers/suggestions would be welcome.
cheers
dim
From | Date | Subject | |
---|---|---|---|
Next Message | Dmitri Colebatch | 2002-06-13 14:32:59 | Re: join question - three tables, two with foreign keys to the first |
Previous Message | Achilleus Mantzios | 2002-06-13 13:19:46 | Re: Another postgres 'file not found' error |