Łączenie wielu kolumn z jednej tabeli do jednej kolumny z innej tabeli
Próbuję nauczyć się łączenia wielu kolumn z jednej tabeli do jednej kolumny z innej tabeli.
Oto moja struktura tabeli w najprostszej formie:
Drużyny
id | team_name |
1 | teamA |
2 | teamB |
3 | teamC |
4 | teamD |
Handel
id | team_1 (FK to teams.id) | team_2 (FK to teams.id) |
1 | 1 | 2 |
2 | 3 | 4 |
To jest mój obecny SQL, który łączy transakcje.team_1 to teams.id:
SELECT teams.team_name AS team1, teams.team_name AS team2, trades.team_1, trades.team_2
FROM teams
JOIN trades ON (trades.team_1 = teams.id);
Moje pytanie brzmi, jak utworzyć drugie dołączenie, które również dołącza do transakcji.team_2 do trades.id?
Oznaczałoby to obie transakcje.team_1 i trades.team_2 zostanie przyłączony do trades.id
Wyniki, które chcę odzyskać, będą następujące:
team1 | team2 | team_1 | team_2 |
teamA | teamB | 1 | 2 |
teamC | teamD | 3 | 4 |
11
4 answers
Tak:
select t1.team_name as team1, t2.team_name as team2, t.team_1, t.team_2
from trades t
inner join teams t1 on t1.id = t.team_1
inner join teams t2 on t2.id = t.team_2;
36
Author: aF.,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-02 15:22:44
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-02 15:22:44
SELECT t1.team_name AS team1, t2.team_name AS t2, tr.team_1, tr.team_2
FROM trades tr
INNER JOIN teams t1 ON t1.id = tr.team_1
INNER JOIN teams t2 ON t2.id = tr.team_2
11
Author: Crontab,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-02 15:22:54
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-02 15:22:54
Spróbuj ponownie dołączyć do tabeli drużyn, ale używając dwóch różnych aliasów:
SELECT
teams1.team_name AS team1,
teams2.team_name AS team2,
trades.team_1,
trades.team_2
FROM trades
JOIN teams AS teams1 ON trades.team_1 = teams1.id
JOIN teams AS teams2 ON trades.team_2 = teams2.id
5
Author: Mark Byers,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-02 15:21:46
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-02 15:21:46
Musisz dołączyć dwa razy:
SELECT t1.team_name as team1, t2.team_name as team2, trades.team_t, trades.team_2
FROM teams t1, teams t2, trades
WHERE t1.id = trades.team_1 and t2.id = trades.team_2
2
Author: Chris,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-02 15:20:58
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-02 15:20:58