[willr]

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:

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 )

The following are the tables and their values;

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);

[requinix]

A table can only provide data for one row at a time. You can't have the teams table showing both the home team and the away team at the same time.

But you can join one table multiple times. Like twice. See where I'm going with this?

[willr]

Thanks for the response requinix. I came up with the following:

SELECT f1.FixtureID, f1.HomeTeam, f1.AwayTeam, t1.TeamName AS TN_H, t2.TeamName AS TN_A 
FROM fixtures f1
LEFT JOIN teams t1 ON f1.HomeTeam = t1.teamid  
LEFT JOIN teams t2 ON f1.AwayTeam = t2.teamid

[SimonJM]

That'd be it, but assuming the teams must exist an INNER JOIN might be better. Also, for clarity I'd have made the aliases at and ht for away team and home team.

[r937]

Originally Posted by SimonJM

... assuming the teams must exist

bravo, good sir

so few people understand foreign key relationships

[willr]

Thanks for the feeback SimonJM.