From: | Gregory Wood <gwood(at)ewebengine(dot)com> |
---|---|
To: | Anony Mous <a(dot)mous(at)shaw(dot)ca> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Two joins on same foreign key |
Date: | 2004-01-31 03:18:49 |
Message-ID: | 401B1E99.6000102@ewebengine.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Davis | 2004-01-31 03:42:27 | Re: PostgreSQL 7.4.1 and pgdb.py |
Previous Message | Lee Harr | 2004-01-31 02:22:09 | Re: Are there commands to enquire about table structure? |