Inner join - two values that are using the same join!

Question

Hello guys. I have had a good think and just can't get my head around this.

I have a table (transfers) like the following

id | playerid | old | new | amount

They are all integers.

I am joining this table (teams) to the following

id | name | founded

The join is going to be on old and new.

old and new are both ints and i need the name from the team table. Is it possible to find out both of these names in one query?

Thanks

Answer 1

Yes it is:

SELECT t.id, 
       t.playerid, 
       t_old.name old_team, 
       t_new.name new_team, 
       t.amount
FROM   transfers t
JOIN   teams t_old ON (t_old.id = t.old)
JOIN   teams t_new ON (t_new.id = t.new);

Note that JOIN is a synonym for INNER JOIN.

Test case:

CREATE TABLE transfers (id int, playerid int, old int, new int, amount int);
CREATE TABLE teams (id int, name varchar(100), founded datetime);

INSERT INTO teams VALUES (1, 'Man Utd', '1900-01-01');
INSERT INTO teams VALUES (2, 'Liverpool', '1890-01-01');

INSERT INTO transfers VALUES (1, 1, 1, 2, 99999999);

Result:

+------+----------+----------+-----------+----------+
| id   | playerid | old_team | new_team  | amount   |
+------+----------+----------+-----------+----------+
|    1 |        1 | Man Utd  | Liverpool | 99999999 |
+------+----------+----------+-----------+----------+
1 row in set (0.00 sec)

Answer 2

Note that if you ever have a record where new matches an entery and old doesn't (or vice versa) the inner join will fail to match. Use outer joins then. but in the case of a transfer, that doesn't make sense. :) Make sure new and old are NOT NULL.