SQL: Linking Two Tables

Question

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 1

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

Answer 2

My suggestion based on my experience is probably one you won't like (I'm including it as an answer because a comment was too long.

In my opinion/experience here is what you need to do. You need to break up the fixtures table.

The fixtures table should have the information that is specific to the fixture itself (date, time, perhaps other specific information)

A related table can be called fixture_participants and includes the team information - each fixture has two rows in fixture participants.

In my solution, I created a column for 'home/away' represented by H/A and wins/losses represented by W/L (integers might be a better solution now that I think about it). I'm thinking this is european football so you should include a column for goals scored.

So you can now easily identify home and away teams you just have to write slightly more involved queries but you can also say 'what is the average goals scored by team A on the road' much more easily.

PS - if you need a sample of what i'm talking about this repository builds a similar table relationship for USA Basketball