Обсуждение: Two joins on same foreign key
Hi,
I’m fairly new to this database, and have read much discussion on sub-queries. I’ve seen that they can be great for some queries, and downright slow for others. I have a table with two foreign keys referencing another table, like:
Table #1
employee_id (pk)
employee_name
Table #2
teamleader_employee_id
backup_employee_id
both fields in table 2 need to do a lookup in table 1 to get the name of the actual employee. Do I need to use nested queries to accomplish this? Any help is greatly appreciated!
-AM
> I’m fairly new to this database, and have read much discussion on > sub-queries. I’ve seen that they can be great for some queries, and > downright slow for others. I have a table with two foreign keys > referencing another table, like: > > Table #1 > > employee_id (pk) > > employee_name > > Table #2 > > teamleader_employee_id > > backup_employee_id > > both fields in table 2 need to do a lookup in table 1 to get the name of > the actual employee. Do I need to use nested queries to accomplish > this? Any help is greatly appreciated! Just do two joins against the first table: SELECT * FROM table2 JOIN table1 tl ON (teamleader_employee_id=tl.employee_id) JOIN table1 b ON (backup_employee_id=b.employee_id); Greg
Anony Mous wrote:
>>I'm fairly new to this database, and have read much discussion on
>>sub-queries. I've seen that they can be great for some queries, and
>>downright slow for others. I have a table with two foreign keys
>>referencing another table, like:
>>
>>Table #1
>>
>>employee_id (pk)
>>
>>employee_name
>>
>>Table #2
>>
>>teamleader_employee_id
>>
>>backup_employee_id
>>
>>both fields in table 2 need to do a lookup in table 1 to get the name of
>>the actual employee. Do I need to use nested queries to accomplish
>>this? Any help is greatly appreciated!
>
>
> Just do two joins against the first table:
>
> SELECT *
> FROM table2
> JOIN table1 tl ON (teamleader_employee_id=tl.employee_id)
> JOIN table1 b ON (backup_employee_id=b.employee_id);
>
> Funny, I still can't get it to work. Postgresql complains there are
> two joins on a single table and won't do it! Are you sure that this
> can be done?
Yes, I do it myself... I've even joined a table to itself. What error
message do you get? Make sure that you assign an alias like my example.
test=# CREATE TABLE table1 (employee_id SERIAL PRIMARY KEY, name VARCHAR);
CREATE TABLE
test=# INSERT INTO table1 (name) VALUES ('Employee 1');
INSERT 104693 1
test=# INSERT INTO table1 (name) VALUES ('Employee 2');
INSERT 104694 1
test=# CREATE TABLE table2 (id SERIAL PRIMARY KEY,
teamleader_employee_id INTEGER REFERENCES table1 (employee_id),
backup_employee_id INTEGER REFERENCES table1 (employee_id));
CREATE TABLE
test=# INSERT INTO table2 (teamleader_employee_id, backup_employee_id)
VALUES (1, 2);
INSERT 104717 1
test=# SELECT *
test-# FROM table2
test-# JOIN table1 tl ON (teamleader_employee_id=tl.employee_id)
test-# JOIN table1 b ON (backup_employee_id=b.employee_id);
id | teamleader_employee_id | backup_employee_id | employee_id |
name | employee_id | name
----+------------------------+--------------------+-------------+------------+-------------+------------
1 | 1 | 2 | 1 |
Employee 1 | 2 | Employee 2
(1 row)
Greg
On Jan 31, 2004, at 7:03 AM, Anony Mous wrote:
> Table #1
> employee_id (pk)
> employee_name
>
> Table #2
> teamleader_employee_id
> backup_employee_id
>
> both fields in table 2 need to do a lookup in table 1 to get the name
> of the actual employee. Do I need to use nested queries to accomplish
> this? Any help is greatly appreciated!
I think you could handle this by calling table1 twice, but with
different aliases, like so
SELECT leader.employee_name, backup.employee_name
FROM table1 leader, table1 backup, table2 t2
WHERE
leader.employee_id = t2.teamleader_employee_id AND
backup.employee_id = t2.backup_employee_id
Does that work for you?
Michael Glaesemann
grzm myrealbox com
Actually, given that there may not always be a backup_employee field (can be
null) I think I'm forced to use an outer join to return all team leader
records regardless if a matching backup_employee record exists. I'll test
yours out and see.
Thank you!
-----Original Message-----
From: Michael Glaesemann [mailto:grzm@myrealbox.com]
Sent: January 30, 2004 11:21 PM
To: Anony Mous
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Two joins on same foreign key
On Jan 31, 2004, at 7:03 AM, Anony Mous wrote:
> Table #1
> employee_id (pk)
> employee_name
>
> Table #2
> teamleader_employee_id
> backup_employee_id
>
> both fields in table 2 need to do a lookup in table 1 to get the name
> of the actual employee. Do I need to use nested queries to accomplish
> this? Any help is greatly appreciated!
I think you could handle this by calling table1 twice, but with
different aliases, like so
SELECT leader.employee_name, backup.employee_name
FROM table1 leader, table1 backup, table2 t2
WHERE
leader.employee_id = t2.teamleader_employee_id AND
backup.employee_id = t2.backup_employee_id
Does that work for you?
Michael Glaesemann
grzm myrealbox com
On Jan 31, 2004, at 3:35 PM, Anony Mous wrote: > Actually, given that there may not always be a backup_employee field > (can be > null) I think I'm forced to use an outer join to return all team leader > records regardless if a matching backup_employee record exists. yup. Actually, I hadn't seen the other responses when I first posted. You've got some options, it looks like. :) Michael Glaesemann grzm myrealbox com
PostgreSQL General Bits Issue #56 has an article on Join Basics which also has an example of multiple table joins. http://cookie.varlena.com/varlena/GeneralBits/56.php --elein elein@varlena.com On Fri, Jan 30, 2004 at 03:03:35PM -0700, Anony Mous wrote: > Hi, > > > > I??m fairly new to this database, and have read much discussion on > sub-queries. I??ve seen that they can be great for some queries, and downright > slow for others. I have a table with two foreign keys referencing another > table, like: > > > > Table #1 > > employee_id (pk) > > employee_name > > > > Table #2 > > teamleader_employee_id > > backup_employee_id > > > > both fields in table 2 need to do a lookup in table 1 to get the name of the > actual employee. Do I need to use nested queries to accomplish this? Any help > is greatly appreciated! > > > > -AM >
I must say, I'm really appreciative with the responses from this list. Thanks to all! -----Original Message----- From: elein [mailto:elein@varlena.com] Sent: January 31, 2004 12:10 PM To: Anony Mous Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Two joins on same foreign key PostgreSQL General Bits Issue #56 has an article on Join Basics which also has an example of multiple table joins. http://cookie.varlena.com/varlena/GeneralBits/56.php --elein elein@varlena.com On Fri, Jan 30, 2004 at 03:03:35PM -0700, Anony Mous wrote: > Hi, > > > > I??m fairly new to this database, and have read much discussion on > sub-queries. I??ve seen that they can be great for some queries, and downright > slow for others. I have a table with two foreign keys referencing another > table, like: > > > > Table #1 > > employee_id (pk) > > employee_name > > > > Table #2 > > teamleader_employee_id > > backup_employee_id > > > > both fields in table 2 need to do a lookup in table 1 to get the name of the > actual employee. Do I need to use nested queries to accomplish this? Any help > is greatly appreciated! > > > > -AM >
Ooops. I've sent the wrong URL for my own darn site. This is the correct URL. http://www.varlena.com/GeneralBits/56.php Sorry for the confusion. --elein On Sat, Jan 31, 2004 at 11:09:37AM -0800, elein wrote: > PostgreSQL General Bits Issue #56 has an article on Join Basics > which also has an example of multiple table joins. > > http://cookie.varlena.com/varlena/GeneralBits/56.php > > --elein > elein@varlena.com > > On Fri, Jan 30, 2004 at 03:03:35PM -0700, Anony Mous wrote: > > Hi, > > > > > > > > I??m fairly new to this database, and have read much discussion on > > sub-queries. I??ve seen that they can be great for some queries, and downright > > slow for others. I have a table with two foreign keys referencing another > > table, like: > > > > > > > > Table #1 > > > > employee_id (pk) > > > > employee_name > > > > > > > > Table #2 > > > > teamleader_employee_id > > > > backup_employee_id > > > > > > > > both fields in table 2 need to do a lookup in table 1 to get the name of the > > actual employee. Do I need to use nested queries to accomplish this? Any help > > is greatly appreciated! > > > > > > > > -AM > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org