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:
id | team_name | 1 | teamA | 2 | teamB | 3 | teamC | 4 | teamD |
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 |
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.