Menu
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 |
667
If you'd like to get data stored in tables joined by a compound key that's a primary key in one table and a foreign key in another table, simply use a join condition on multiple columns. In one joined table (in our example, enrollment ), we have a primary key built from two columns ( student_id and course_code ).
SELECT * FROM table1 JOIN table2 ON table1. column_name = table2. column_name; The INNER JOIN in SQL joins two tables according to the matching of a certain criteria using a comparison operator.
Four types of joins: left, right, inner, and outer.
Query: SELECT *, CONCAT(FIRSTNAME, LASTNAME) AS FIRSTNAME FROM demo_table; Output: Here, we can see that FIRSTNAME and LASTNAME is concatenated but there is no space between them, If you want to add space between the FIRSTNAME and LASTNAME then add space(' ') in CONCAT() function.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
