join question - three tables, two with foreign keys to the first
От | Dmitri Colebatch |
---|---|
Тема | join question - three tables, two with foreign keys to the first |
Дата | |
Msg-id | 08af01c212e4$7a12a180$fe00a8c0@hobbes обсуждение исходный текст |
Ответы |
Re: join question - three tables, two with foreign keys to the first
|
Список | 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
В списке pgsql-sql по дате отправления: