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

> why do you expect nulls?

probably because my sql is extremely rusty (o:

> 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.

yes, as Stephan Szabo wrote:
>> 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.

that makes perfect sense. What I wanted is what you have given below (I
think - I've only looked quickly so far). I suppose I want to do the left
outer join on leave, and a left outer join on pay - I dont want to join the
results of the first join with the second - as the pay and leave tables are
unrelated - except for the fact that they both have a fk to emp.

> 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?

no relationship. what I wanted is:
- for each row in employee, select all matching records in pay
- for each row in employee, select all matching records in leave
- if no records match, select the matching record in employee alone.

from memory, oracle would do this by sql somehting like:

select emp.name, lv.from_date, lv.to_date, pay.amount
from employee as emp, employee_leave as lv, employee_pay as pay
where
emp.id = (+) lv.employee_id and
emp.id = (+) pay.employee_id
where emp.id = 1

(although I can never remember the side that the + goes on....)

> 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;

yes, I think this is what I want.... which is effectively just the same as
below yes?

> or, if lv and pay are unrelated, why not two queries?

I was wanting to only have one trip to the database. I've always been
taught to avoid multiple db trips where possible.....

thanks for your help - much appreciated.

cheers
dim

>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster