By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,564 Members | 956 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,564 IT Pros & Developers. It's quick & easy.

Two joins on same foreign key

P: n/a
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
Nov 22 '05 #1
Share this Question
Share on Google+
7 Replies

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #2

P: n/a

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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #3

P: n/a
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:gr**@myrealbox.com]
Sent: January 30, 2004 11:21 PM
To: Anony Mous
Cc: pg***********@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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #4

P: n/a

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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #5

P: n/a
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
el***@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 ma*******@postgresql.org

Nov 22 '05 #6

P: n/a
I must say, I'm really appreciative with the responses from this list.
Thanks to all!


-----Original Message-----
From: elein [mailto:el***@varlena.com]
Sent: January 31, 2004 12:10 PM
To: Anony Mous
Cc: pg***********@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
el***@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 7: don't forget to increase your free space map settings

Nov 22 '05 #7

P: n/a

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
el***@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 ma*******@postgresql.org


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.