Getting multiple values from multiple tables

Question

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?

HoneyBadger · Accepted Answer · 2018-02-08 15:28:52Z

1

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 *.

answered Feb 8, 2018 at 15:28

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting multiple values from multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related