pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedjoin question - three tables, two with foreign keys to the first
5+ messages / 3 participants
[nested] [flat]
* join question - three tables, two with foreign keys to the first
@ 2002-06-13 14:13 Dmitri Colebatch <dim@bigpond.net.au>
2002-06-13 14:32 ` Re: join question - three tables, two with foreign keys to the first Dmitri Colebatch <dim@bigpond.net.au>
2002-06-13 15:19 ` Re: join question - three tables, two with foreign keys to the first Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 2 replies; 5+ messages in thread
From: Dmitri Colebatch @ 2002-06-13 14:13 UTC (permalink / raw)
To: pgsql-sql
hey all,
here's my situation. three tables:
create table employee (id integer primary key, name varchar(32));
create table employee_leave (id integer primary key, employee_id integer,
from_date date, to_date date, constraint emp_leave_fk foreign key
(employee_id) references employee (id));
create table employee_pay (id integer primary key, employee_id integer,
amount integer, constraint emp_pay_fk foreign key (employee_id) references
employee (id));
and some sample data:
insert into employee (id, name) values (1, 'dim');
insert into employee_leave (id, employee_id, from_date, to_date) values (2,
1, '10-05-2002', '14-05-2002');
insert into employee_leave (id, employee_id, from_date, to_date) values (1,
1, '10-06-2002', '14-06-2002');
insert into employee_pay(id, employee_id, amount) values (1, 1, 100);
insert into employee_pay(id, employee_id, amount) values (2, 1, 100);
and I want to retrieve the information for an employee (all pay, and all
leave) in one query.... here's what I've got
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.... I want to be able to
iterate through the resultset and determine if the record is from the leave
table or pay table - but because I dont get null results, I cant....
any pointers/suggestions would be welcome.
cheers
dim
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: join question - three tables, two with foreign keys to the first
2002-06-13 14:13 join question - three tables, two with foreign keys to the first Dmitri Colebatch <dim@bigpond.net.au>
@ 2002-06-13 14:32 ` Dmitri Colebatch <dim@bigpond.net.au>
2002-06-13 15:21 ` Re: join question - three tables, two with foreign keys to Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 1 reply; 5+ messages in thread
From: Dmitri Colebatch @ 2002-06-13 14:32 UTC (permalink / raw)
To: pgsql-sql
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?
ta again
dim
----- Original Message -----
From: "Dmitri Colebatch" <dim@bigpond.net.au>
To: <pgsql-sql@postgresql.org>
Sent: Friday, June 14, 2002 12:13 AM
Subject: [SQL] join question - three tables, two with foreign keys to the
first
> hey all,
>
> here's my situation. three tables:
>
> create table employee (id integer primary key, name varchar(32));
> create table employee_leave (id integer primary key, employee_id integer,
> from_date date, to_date date, constraint emp_leave_fk foreign key
> (employee_id) references employee (id));
> create table employee_pay (id integer primary key, employee_id integer,
> amount integer, constraint emp_pay_fk foreign key (employee_id) references
> employee (id));
>
> and some sample data:
>
> insert into employee (id, name) values (1, 'dim');
> insert into employee_leave (id, employee_id, from_date, to_date) values
(2,
> 1, '10-05-2002', '14-05-2002');
> insert into employee_leave (id, employee_id, from_date, to_date) values
(1,
> 1, '10-06-2002', '14-06-2002');
> insert into employee_pay(id, employee_id, amount) values (1, 1, 100);
> insert into employee_pay(id, employee_id, amount) values (2, 1, 100);
>
> and I want to retrieve the information for an employee (all pay, and all
> leave) in one query.... here's what I've got
>
> 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.... I want to be able
to
> iterate through the resultset and determine if the record is from the
leave
> table or pay table - but because I dont get null results, I cant....
>
> any pointers/suggestions would be welcome.
>
> cheers
> dim
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: join question - three tables, two with foreign keys to
2002-06-13 14:13 join question - three tables, two with foreign keys to the first Dmitri Colebatch <dim@bigpond.net.au>
2002-06-13 14:32 ` Re: join question - three tables, two with foreign keys to the first Dmitri Colebatch <dim@bigpond.net.au>
@ 2002-06-13 15:21 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2002-06-13 15:21 UTC (permalink / raw)
To: Dmitri Colebatch <dim@bigpond.net.au>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: join question - three tables, two with foreign keys to the first
2002-06-13 14:13 join question - three tables, two with foreign keys to the first Dmitri Colebatch <dim@bigpond.net.au>
@ 2002-06-13 15:19 ` Manfred Koizar <mkoi-pg@aon.at>
2002-06-13 22:38 ` Re: join question - three tables, two with foreign keys to the first Dmitri Colebatch <dim@bigpond.net.au>
1 sibling, 1 reply; 5+ messages in thread
From: Manfred Koizar @ 2002-06-13 15:19 UTC (permalink / raw)
To: Dmitri Colebatch <dim@bigpond.net.au>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: join question - three tables, two with foreign keys to the first
2002-06-13 14:13 join question - three tables, two with foreign keys to the first Dmitri Colebatch <dim@bigpond.net.au>
2002-06-13 15:19 ` Re: join question - three tables, two with foreign keys to the first Manfred Koizar <mkoi-pg@aon.at>
@ 2002-06-13 22:38 ` Dmitri Colebatch <dim@bigpond.net.au>
0 siblings, 0 replies; 5+ messages in thread
From: Dmitri Colebatch @ 2002-06-13 22:38 UTC (permalink / raw)
To: Manfred Koizar <mkoi-pg@aon.at>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-06-13 22:38 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-13 14:13 join question - three tables, two with foreign keys to the first Dmitri Colebatch <dim@bigpond.net.au>
2002-06-13 14:32 ` Dmitri Colebatch <dim@bigpond.net.au>
2002-06-13 15:21 ` Re: join question - three tables, two with foreign keys to Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-06-13 15:19 ` Manfred Koizar <mkoi-pg@aon.at>
2002-06-13 22:38 ` Dmitri Colebatch <dim@bigpond.net.au>
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