From: Dmitri Colebatch <dim@bigpond.net.au>
To: Manfred Koizar <mkoi-pg@aon.at>
Cc: pgsql-sql@postgresql.org
Subject: Re: join question - three tables, two with foreign keys to the first
Date: Fri, 14 Jun 2002 08:38:39 +1000
Message-ID: <08ec01c2132b$107faf00$fe00a8c0@hobbes> (raw)
References: <08af01c212e4$7a12a180$fe00a8c0@hobbes>
<0mchgus8o3g05b3e9nh1t0sebrijaskdae@4ax.com>
> 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
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: dim@bigpond.net.au, mkoi-pg@aon.at
Subject: Re: join question - three tables, two with foreign keys to the first
In-Reply-To: <08ec01c2132b$107faf00$fe00a8c0@hobbes>
* 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