Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id AA56D476C25
	for <pgsql-sql@postgresql.org>; Thu, 13 Jun 2002 11:25:43 -0400 (EDT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by postgresql.org (Postfix) with ESMTP id AFFF7476F27
	for <pgsql-sql@postgresql.org>; Thu, 13 Jun 2002 11:21:27 -0400 (EDT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id A253AD606; Thu, 13 Jun 2002 08:21:28 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 97CAD5C01; Thu, 13 Jun 2002 08:21:28 -0700 (PDT)
Date: Thu, 13 Jun 2002 08:21:28 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Dmitri Colebatch <dim@bigpond.net.au>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: join question - three tables, two with foreign keys to
In-Reply-To: <08da01c212e7$37bf1450$fe00a8c0@hobbes>
Message-ID: <20020613081443.A4208-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Archive-Number: 200206/174
X-Sequence-Number: 8139


On Fri, 14 Jun 2002, Dmitri Colebatch wrote:

> 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 |    100
>  dim  | 2002-10-05 | 2002-05-14 |    100
>  dim  | 2002-10-06 | 2002-06-14 |    100
>  dim  | 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)        |    100
>  dim  |       (null)      |      (null)        |    100
> (4 rows)
>
> am I missing something?

I don't see why you'd expect that.
Both rows in employee_leave match and both rows
in employee_pay match.  They're not unrelated joins,
you're asking to join employee with employee_leave
and then join the results of that with employee_pay.

Perhaps you want a union? Something like:
 select emp.name, lv.from_date, lv.to_date, null as amount
 from employee as emp, employee_leave as lv where emp.id=
 lv.employee_id
union
 select emp.name, null, null, pay.amount
 from employee as emp, employee_pay as pay where emp.id=
 pay.employee_id

If you want to get a row for an employee even when they
have neither leave nor pay, you can use left joins above,
but that'll give you some rows that'll be like
<name> NULL NULL NULL.