CODEWITHSUNDEEP
mysqlsql
Lee Price
Lee Price

Reputation: 5212

Selecting 2 columns from a MySQL db with join

I have the following tables:

Teams

+------+--------------+--------------+
| id   |  team_name   |  team_code   |
+------+--------------+--------------+
|  1   |  Wales       | WAL          |
|  2   |  England     | ENG          |
|  3   |  New Zealand | NZL          |
+------+--------------+--------------+

Matches

+------+-----------+-----------+
| id   |  team_a   |  team_b   |
+------+-----------+-----------+
|  1   |  WAL      | ENG       |
|  2   |  ENG      | NZL       |
|  3   |  WAL      | NZL       |
+------+-----------+-----------+

I know how a join works, but I can't get my head around how I can query to the database by Matched.id to get the team name for BOTH teams from the Teams database. Let me explain better

Hope I've explained my problem clearly enough, but If I haven't please feel free to ask more questions

Upvotes: 1

Views: 70

Answers (3)

Oded
Oded

Reputation: 498904

The database structure seems to be "off" to me.

You should have both team_a and team_b as foreign keys to the Teams table primary key.

You can then join on the IDs (twice) to get the full names.

Matches:

+------+-----------+-----------+
| id   |  team_a   |  team_b   |
+------+-----------+-----------+
|  1   |  1        | 2         |
|  2   |  2        | 3         |
|  3   |  1        | 3         |
+------+-----------+-----------+

SELECT T1.team_name, T2.team_name
FROM Matches M
  INNER JOIN Teams T1
    ON M.team_a = T1.id
  INNER JOIN Teams T2
    ON M.team_b = T2.id
WHERE M.id = 1

Upvotes: 6

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

select 
    m.id, 
    t1.team_name, 
    t2.team_name
from 
    Matches m
    inner join Teams t1 on m.team_a = t1.team_code
    inner join Teams t2 on m.team_b = t2.team_code

Upvotes: 0

podiluska
podiluska

Reputation: 51494

Select
    matches.*, 
    teamA.team_name as TeamA_Name, 
    teamB.team_name as TeamB_Name
from matches
   inner join teams teamA on matches.team_a = TeamA.team_code
   inner join teams teamB on matches.team_b = TeamB.team_code
where 
   matches.id = 1

Upvotes: 1

Related Questions