CODEWITHSUNDEEP
mysqlsql
Ruben Bårdsen
Ruben Bårdsen

Reputation: 15

Getting multiple values from multiple tables

I have a database setup in MySQL, with two different tables:

Table "matches"

ID   TEAM1ID   TEAM2ID   TIME   DATE
-------------------------------------
5    13        14        21:00  5 May
5    4         7        22:00  7 May

Table "teams"

ID   NAME             TEAMLEADER
-----------------------------------
13   GucciGang        Peter
4    Beatles          Peter
14   Beachboys        Peter
7    BackstreetBoys   Peter

I want to be able to assosicate the IDs in table "matches" with the team names from table "teams", so I can print it out on a matchpage.

My problem is that I can't the query to suit my needs.

My latest attempt:

SELECT matches.*, teams.name AS team1name
FROM matches
LEFT JOIN teams ON matches.team1ID = teams.id
UNION
SELECT matches.*, teams.name AS team2name
FROM matches
LEFT JOIN teams ON matches.team2ID = teams.id

This is obviously not ideal, as I get all the match data twice.
This gives me both names, but they are on seperate rows and they are both labeled team1name.

The result I'm looking for is getting something like this:

ID TEAM1ID   TEAM2ID  TIME   DATE   TEAM1NAME   TEAM2NAME
---------------------------------------------------------
5  13        14       21:00  5 May  GucciGang   BeachBoys

Is this possible?

Upvotes: 1

Views: 19

Answers (1)

HoneyBadger
HoneyBadger

Reputation: 15140

Simply join the table twice:

SELECT    matches.*
,         Team1.name AS team1name
,         Team2.name AS team2name
FROM      matches
LEFT JOIN teams AS Team1
       ON matches.team1ID = Team1.id
LEFT JOIN teams AS Team2
       ON matches.team2ID = Team2.id

Note it's best practice to specify columns specifically, rather than SELECT *.

Upvotes: 1

Related Questions