From: Manfred Koizar <mkoi-pg@aon.at>
To: Dmitri Colebatch <dim@bigpond.net.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: join question - three tables, two with foreign keys to the first
Date: Thu, 13 Jun 2002 17:19:08 +0200
Message-ID: <0mchgus8o3g05b3e9nh1t0sebrijaskdae@4ax.com> (raw)
In-Reply-To: <08af01c212e4$7a12a180$fe00a8c0@hobbes>
References: <08af01c212e4$7a12a180$fe00a8c0@hobbes>
On Fri, 14 Jun 2002 00:13:22 +1000, "Dmitri Colebatch"
<dim@bigpond.net.au> wrote:
>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....
Dmitri,
why do you expect nulls?
SELECT ... FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col2;
returns nulls for the b-columns in the select list, if you have a row
in t1 with a value t1.col1, that does not appear as col2 in any row of
t2. In your example, however, you select a single row from emp with
id = 1, and there are two rows in lv with employee_id = 1 and two rows
in pay with employee_id = 1.
And I doubt, you want to get the same row from lv more than once, only
because there are multiple matches in pay, and vice versa. Add lv.id
and pay.id to your SELECT to see what I mean. You may expect to get 4
rows, but what you get is not 2+2, but 2*2. Add some more rows and
test again. Isn't there any relationship between lv and pay?
I don't know if I understand your problem. Propably you want:
SELECT emp.name, lv.from_date, lv.to_date, NULL AS amount
FROM employee AS emp
LEFT OUTER JOIN employee_leave AS lv ON emp.id = lv.employee_id
WHERE emp.id = 1
UNION ALL
SELECT emp.name, NULL AS from_date, NULL AS to_date, pay.amount
FROM employee AS emp
LEFT OUTER JOIN employee_pay AS pay ON emp.id = pay.employee_id
WHERE emp.id = 1;
or, if lv and pay are unrelated, why not two queries?
SELECT emp.name, lv.from_date, lv.to_date
FROM employee AS emp
LEFT OUTER JOIN employee_leave AS lv ON emp.id = lv.employee_id
WHERE emp.id = 1;
SELECT emp.name, pay.amount
FROM employee AS emp
LEFT OUTER JOIN employee_pay AS pay ON emp.id = pay.employee_id
WHERE emp.id = 1;
HTH.
Servus
Manfred
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mkoi-pg@aon.at, dim@bigpond.net.au
Subject: Re: join question - three tables, two with foreign keys to the first
In-Reply-To: <0mchgus8o3g05b3e9nh1t0sebrijaskdae@4ax.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox