Question 199969 in Mysql Question posted in
The official MySQL documentation can be found here.

populate a query with information from a different mysql table

team1id and team2id should link with teamId from another table and populate the query as teamName. The intended query result should look like select * from matches but populated by teamName in the result. Having a hard time getting my syntax correct.

mysql> select * from matches;
+----+---------+---------+---------------------+
| id | team1id | team2id | date                |
+----+---------+---------+---------------------+
|  1 | 1       | 2       | 2023-07-26 03:12:00 |
+----+---------+---------+---------------------+
1 row in set (0.00 sec)

mysql> select * from teamNames;
+--------+----------+---------------+
| teamId | teamName | teamCaptainId |
+--------+----------+---------------+
|      1 | cdx      | 1             |
|      2 | k4h      | 2             |
+--------+----------+---------------+
2 rows in set (0.00 sec)

my intended result would look like:

+----+---------+---------+---------------------+
| id | team1id | team2id | date                |
+----+---------+---------+---------------------+
|  1 | cdx     | k4h     | 2023-07-26 03:12:00 |
+----+---------+---------+---------------------+
1 row in set (0.00 sec)

I have tried variations on inner join on the two tables but only result that came close populated both team1id and team2id with either cdx or k4h. when the result i am trying to receive would show two different teamName’s that are playing against each other on 2023 07 26

Tags:

2

Answers


  1. 0

    You’re looking to join the teamNames table twice, once for each Id:

    select m.id, n1.teamName Team1_Name, n2.teamName Team2_Name, m.date
from matches m
join teamNames n1 on n1.teamId = m.team1Id
join teamNames n2 on n2.teamId = m.team2Id;
    Login or Signup to reply.
  2. 0

    You can also try a correlated subquery:

    select id ,
(select teamName from teamNames where teamId=m.team1id) as team1id,
(select teamName from teamNames where teamId=m.team2id) as team2id,
date
from matches m;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search