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