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

[color=blue]
> 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![/color]

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

On Jan 31, 2004, at 7:03 AM, Anony Mous wrote:[color=blue]
> 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![/color]

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

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:[color=blue]
> 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![/color]

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

On Jan 31, 2004, at 3:35 PM, Anony Mous wrote:
[color=blue]
> 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.[/color]

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

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:[color=blue]
> 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
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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:[color=blue]
> 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[/color]
downright[color=blue]
> 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[/color]
the[color=blue]
> actual employee. Do I need to use nested queries to accomplish this? Any[/color]
help[color=blue]
> is greatly appreciated!
>
>
>
> -AM
>[/color]


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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:[color=blue]
> 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:[color=green]
> > 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
> >[/color]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org[/color]

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