Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Question Unanswered: Linking two tables and the same field

    ok, to some of you this sound basic but i'm really at a loss!

    using this basic query

    SELECT fixtures.hometeam, fixtures.awayteam
    FROM fixtures
    WHERE fixtures.hometeam = 17 OR fixtures.awayteam = 17;

    I am able to produce the results

    hometeam awayteam
    13 17
    3 17
    17 16
    81 17

    What I want to do is replace the number (which is the team ID) with the team name.

    I have a seperate team table with name and id

    I've tried adding something similar to fixtues.hometeam = team.id but that just displayes all the teams in the team table

    If you can make any sense of this i'd be greatful for anyhelp!

  2. #2
    Join Date
    Apr 2003
    Location
    Edison, NJ / Oakland, NJ (Work)
    Posts
    32
    You have to use two different reference names for the table when choosing the fields to return (t and t2) then join the same table twice with different criteria. If the table names are different you should be able to get the gist.

    Hope this helps!

    Code:
    SELECT t.teamName as Home, t2.teamName as Away 
    FROM fixtures as f 
        JOIN team as t 
           ON t.teamID = f.hometeam 
        JOIN team as t2 
           ON t2.teamID = f.awayteam 
    WHERE f.hometeam = 17 or f.awayteam = 17
    It gave me this result...

    Home Away
    Team 13 Team 17
    Team 3 Team 17
    Team 17 Team 16
    Team 81 Team 17

    Here's the data I used...
    Code:
    #
    # Table structure for table `fixtures`
    #
    
    CREATE TABLE `fixtures` (
      `fixID` int(2) NOT NULL auto_increment,
      `hometeam` int(2) NOT NULL default '0',
      `awayteam` int(2) NOT NULL default '0',
      PRIMARY KEY  (`fixID`)
    ) TYPE=MyISAM AUTO_INCREMENT=5 ;
    
    #
    # Dumping data for table `fixtures`
    #
    
    INSERT INTO `fixtures` VALUES (1, 13, 17);
    INSERT INTO `fixtures` VALUES (2, 3, 17);
    INSERT INTO `fixtures` VALUES (3, 17, 16);
    INSERT INTO `fixtures` VALUES (4, 81, 17);
    
    #
    # Table structure for table `team`
    #
    
    CREATE TABLE `team` (
      `teamID` int(2) NOT NULL auto_increment,
      `teamName` varchar(50) NOT NULL default '',
      PRIMARY KEY  (`teamID`),
      UNIQUE KEY `teamName` (`teamName`)
    ) TYPE=MyISAM AUTO_INCREMENT=82 ;
    
    #
    # Dumping data for table `team`
    #
    
    INSERT INTO `team` VALUES (1, 'Team 1');
    INSERT INTO `team` VALUES (2, 'Team 2');
    INSERT INTO `team` VALUES (3, 'Team 3');
    INSERT INTO `team` VALUES (4, 'Team 4');
    INSERT INTO `team` VALUES (5, 'Team 5');
    INSERT INTO `team` VALUES (6, 'Team 6');
    INSERT INTO `team` VALUES (7, 'Team 7');
    INSERT INTO `team` VALUES (8, 'Team 8');
    INSERT INTO `team` VALUES (9, 'Team 9');
    INSERT INTO `team` VALUES (10, 'Team 10');
    INSERT INTO `team` VALUES (11, 'Team 11');
    INSERT INTO `team` VALUES (12, 'Team 12');
    INSERT INTO `team` VALUES (13, 'Team 13');
    INSERT INTO `team` VALUES (14, 'Team 14');
    INSERT INTO `team` VALUES (15, 'Team 15');
    INSERT INTO `team` VALUES (16, 'Team 16');
    INSERT INTO `team` VALUES (17, 'Team 17');
    INSERT INTO `team` VALUES (81, 'Team 81');
    Last edited by smithhayward; 02-19-04 at 09:36.
    -----------------------------------
    Smith Hayward
    -----------------------------------

  3. #3
    Join Date
    Feb 2004
    Posts
    7
    fantastic mate, needed a bit of tweaking to change to my database... now i'll exend it a bit and hopefully include the league name date etc...

    Thanks again

    jimmy

  4. #4
    Join Date
    Apr 2003
    Location
    Edison, NJ / Oakland, NJ (Work)
    Posts
    32
    Glad to help!
    -----------------------------------
    Smith Hayward
    -----------------------------------

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •