Re: Two joins on same foreign key

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