Re: join question - three tables, two with foreign keys to the first

maybe just to qualify, I get this:

select emp.name, lv.from_date, lv.to_date, pay.amount
from employee as emp
left join employee_leave as lv on emp.id = lv.employee_id
left join employee_pay as pay on emp.id = pay.employee_id
where emp.id = 1;
name | from_date  |  to_date   | amount
------+------------+------------+--------dim  | 2002-10-05 | 2002-05-14 |    100dim  | 2002-10-05 | 2002-05-14 |
100dim | 2002-10-06 | 2002-06-14 |    100dim  | 2002-10-06 | 2002-06-14 |    100
 
(4 rows)

but would expect the results to be
name | from_date  |  to_date   | amount
------+------------+------------+--------dim  | 2002-10-05 | 2002-05-14 |   (null)dim  | 2002-10-05 | 2002-05-14 |
(null)dim |       (null)      |      (null)        |    100dim  |       (null)      |      (null)        |    100
 
(4 rows)

am I missing something?

ta again
dim

----- Original Message -----
From: "Dmitri Colebatch" <dim@bigpond.net.au>
To: <pgsql-sql@postgresql.org>
Sent: Friday, June 14, 2002 12:13 AM
Subject: [SQL] join question - three tables, two with foreign keys to the
first


> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>