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

On Fri, 14 Jun 2002 00:13:22 +1000, "Dmitri Colebatch"
<dim(at)bigpond(dot)net(dot)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