Alan Tingey Alan Tingey - 1 month ago 18
MySQL Question

SQL: Linking Two Tables

Let me start my saying I have the following two tables:

Fixtures Table:

| straightred_fixture | CREATE TABLE `straightred_fixture` (
`fixtureid` int(11) NOT NULL,
`hometeamscore` int(11) DEFAULT NULL,
`awayteamscore` int(11) DEFAULT NULL,
`homegoaldetails` longtext,
`awaygoaldetails` longtext,
`awayteamid` int(11) NOT NULL,
`hometeamid` int(11) NOT NULL,
PRIMARY KEY (`fixtureid`),
KEY `straightred_fixture_2e879a39` (`awayteamid`),
KEY `straightred_fixture_bcb6decb` (`hometeamid`),
KEY `straightred_fixture_d6d641f1` (`soccerseasonid`),
KEY `straightred_fixture_fixturematchday2_f98c3a75_uniq` (`fixturematchday`),
CONSTRAINT `D9b896edf0aff4d9b5c00682a8e21ea3` FOREIGN KEY (`fixturematchday`) REFERENCES `straightred_fixturematchday` (`fixturematchdayid`),
CONSTRAINT `straightr_soccerseasonid_92496b92_fk_straightred_season_seasonid` FOREIGN KEY (`soccerseasonid`) REFERENCES `straightred_season` (`seasonid`),
CONSTRAINT `straightred_fixtu_awayteamid_3d1961ba_fk_straightred_team_teamid` FOREIGN KEY (`awayteamid`) REFERENCES `straightred_team` (`teamid`),
CONSTRAINT `straightred_fixtu_hometeamid_6e37e94b_fk_straightred_team_teamid` FOREIGN KEY (`hometeamid`) REFERENCES `straightred_team` (`teamid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


Team Table:

| straightred_team | CREATE TABLE `straightred_team` (
`teamid` int(11) NOT NULL,
`teamname` varchar(36) NOT NULL,
`country` varchar(36) DEFAULT NULL,
`stadium` varchar(36) DEFAULT NULL,
`homepageurl` longtext,
`wikilink` longtext,
`teamcode` varchar(5) DEFAULT NULL,
`teamshortname` varchar(24) DEFAULT NULL,
`currentteam` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`teamid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


The Query::

select hometeamid as team, awayteamid as opponent, hometeamscore as team_score, awayteamscore as opponent_score,
(case when (hometeamscore-awayteamscore)>0 then 'W' when (hometeamscore-awayteamscore)<0 then 'L' ELSE 'D' END) as result, 'home' as mstatus
from straightred_fixture sf, straightred_team st;


What I want to do is return the team name rather than the id of the teams in the query above. I realize some sort of join is required but I am getting myself in a right muddle.

Answer

You need to join on the teams table twice, once for the home team and once for the away team:

SELECT home.teamname AS team, 
       away.teamname AS opponent, 
       hometeamscore AS team_score, 
       awayteamscore AS opponent_score, 
       CASE WHEN (hometeamscore - awayteamscore) > 0 then 'W'
            WHEN (hometeamscore - awayteamscore) < 0 then 'L' 
            ELSE 'D' 
       END AS result, 
       'home' AS mstatus
FROM   straightred_fixture sf
JOIN   straightred_team home ON hometeamid = home.teamid
JOIN   straightred_team away ON awayteamid = away.teamid