Hi,
Thanks for having a look at this post. I’ve been away from programming for a few years. I’m recently trying to get back into it and I’ve ran into a few problems. I’ve been trying to develop a database that stores football results, along with a front end that displays the results. I’ve created two tables called fixtures and teams with the following columns:
fixtures
- FixtureID
- League
- GameDate
- HomeTeam
- HomeScore
- AwayTeam
- AwayScore
teams
- TeamID
- TeamName
- Leagueid
The HomeTeam and AwayTeam columns in the fixtures table have the TeamID stored. The TeamID is used to identify the Team Name in the teams table. What I'm trying to do is retrieve the list of all the fixtures, however, rather than display the Team ID I want the team Name to be displayed instead. I've tried doing a join but it doesn't work the way I would like it to, anyone able to help out? Thanks.
Here is my join query attempt:
The following are the tables and their values;Code:SELECT fixtures.FixtureID, fixtures.HomeTeam, fixtures.AwayTeam, teams.TeamID, teams.TeamID as TID, teams.TeamName AS TN FROM fixtures LEFT JOIN teams ON teams.teamid IN ( fixtures.hometeam, fixtures.awayteam )
Code:CREATE TABLE `teams` ( `TeamID` tinyint(4) NOT NULL, `TeamName` text NOT NULL, `Leagueid` tinyint(4) DEFAULT NULL, `weekly_results` varchar(1500) NOT NULL ) INSERT INTO `teams` (`TeamID`, `TeamName`) VALUES (1, 'Chelsea'), (2, 'Man U.'), (3, 'Arsenal'), (4, 'Liverpool'), (9, 'Newcastle U'); CREATE TABLE `fixtures` ( `FixtureID` int(5) NOT NULL, `League` tinyint(4) DEFAULT NULL, `GameDate` date NOT NULL DEFAULT '0000-00-00', `HomeTeam` tinyint(4) DEFAULT NULL, `HomeScore` tinyint(4) DEFAULT NULL, `AwayTeam` tinyint(4) DEFAULT NULL, `AwayScore` tinyint(4) DEFAULT NULL ) INSERT INTO `fixtures` (`FixtureID`, `League`, `GameDate`, `HomeTeam`, `HomeScore`, `AwayTeam`, `AwayScore`) VALUES (1, 1, '2006-03-01', 1, 4, 2, 4), (2, 1, '2006-03-01', 3, 4, 4, 4), (6, 1, '2006-03-08', 1, 2, 4, 2), (7, 1, '2018-06-01', 9, 0, 1, 1), (8, 1, '0000-00-00', 9, 0, 4, 2), (9, 1, '2018-06-03', 9, 2, 3, 1);