CODEWITHSUNDEEP
mysqlsqldatabasejoininner-join
Ryan Gray
Ryan Gray

Reputation: 3

MySQL - displaying two inner joins in separate columns

First of all I have this which returns the date of all the football games where

HomeShotsOnTarget(HST) = FullTimeHomeGoals(FTHG)

or

AwayShotsOnTarget(AST) = FullTimeAWayGoals(FTHG)

SELECT MatchDate, HomeTeam, AwayTeam 
FROM matches 
WHERE HST=FTHG or AST=FTAG

This displays

MatchDate | HomeTeam | AwayTeam
2003/08/23    17          32
2003/09/13    24          39

and so on and so on...

The numbers under HomeTeam and AwayTeam are the TeamCodes which are in another table called clubs which also has the teams real name.

The following matches the TeamCode for the HomeTeam with the RealName in table clubs.

SELECT MatchDate, RealName
FROM club T1 
INNER JOIN matches T2 ON T1.TeamCode = T2.HomeTeam

This displays

MatchDate|  RealName|
2003/08/23  Arsenal
2003/09/13 Blackburn

Etc...

So my problem is I can't seem to find a way that displays the RealName Under HomeTeam and AwayTeam instead of the TeamCode. Like this...

MatchDate | HomeTeam | AwayTeam
2003/08/23   Arsenal   Aston Villa
2003/09/13   Blackburn  Man Utd

Upvotes: 0

Views: 78

Answers (3)

Martin Schneider
Martin Schneider

Reputation: 3268

You just have to join the Team-Table two times, try this query:

SELECT 
  MatchDate, 
  T1.RealName, 
  T2.RealName 
FROM 
  matches INNER JOIN club T1 ON (matches.HomeTeam = T1.TeamCode)
          INNER JOIN club T2 ON (matches.AwayTeam = T2.TeamCode)
WHERE 
  HST=FTHG OR AST=FTAG

Upvotes: 0

SlimsGhost
SlimsGhost

Reputation: 2909

You just need to do another join to the club table for the Away Team, like this:

SELECT MatchDate, T1.RealName AS HomeTeamName, T3.RealName AS AwayTeamName 
FROM club T1 
INNER JOIN matches T2 ON T1.TeamCode = T2.HomeTeam 
INNER JOIN club T3 ON T3.TeamCode = T2.AwayTeam

Upvotes: 0

Rubico
Rubico

Reputation: 474

Maybe something like:

SELECT MatchDate, homeTeam.RealName AS HomeTeam, awayTeam.RealName AS AwayTeam
FROM matches m
INNER JOIN club homeTeam ON (m.HomeTeam = homeTeam.TeamCode)
INNER JOIN club awayTeam ON (m.AwayTeam = awayTeam.TeamCode);

I use to put some meaning labels instead of just, T1 and T2.

Upvotes: 1

Related Questions