#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    13
    Rep Power
    0

    joining results from two tables


    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:
    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 )
    The following are the tables and their values;

    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);
  2. #2
  3. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,820
    Rep Power
    9646
    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?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    13
    Rep Power
    0
    Thanks for the response requinix. I came up with the following:

    Code:
    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
  6. #4
  7. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,674
    Rep Power
    1841
    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.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,793
    Rep Power
    4331
    Originally Posted by SimonJM
    ... assuming the teams must exist
    bravo, good sir

    so few people understand foreign key relationships
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    13
    Rep Power
    0
    Thanks for the feeback SimonJM.

IMN logo majestic logo threadwatch logo seochat tools logo