Join multiple columns from one table to single column from another table – This article will take you through the common SQL errors that you might encounter while working with mysql, sql, database. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
I’m trying to learn how to join multiple columns from one table to a single column from another table.
This is my table structure in its simplest form:
teams
id | team_name |
1 | teamA |
2 | teamB |
3 | teamC |
4 | teamD |
trades
id | team_1 (FK to teams.id) | team_2 (FK to teams.id) |
1 | 1 | 2 |
2 | 3 | 4 |
This is my current SQL which joins trades.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);
My question is, how do I create a second join that also joins trades.team_2 to trades.id?
This would mean both trades.team_1 AND trades.team_2 would be joined to trades.id
The results I want to get back would be:
team1 | team2 | team_1 | team_2 |
teamA | teamB | 1 | 2 |
teamC | teamD | 3 | 4 |
Solution :
Like this:
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;
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
Try joining the teams table again but using two different aliases:
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
You need to join twice:
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
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.