Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 25
  1. #1
    Junior Member
    Join Date
    Dec 2005
    Location
    Vilnius, Lithuania
    Posts
    17
    Member #
    12192
    Hello,

    I'm trying to build a table of sport competition results. Matches can be stored in a table like:

    res
    tour | team_home | team_guest | score

    i also want to keep team names separately in a different table:

    team
    team_id | team

    The idea is to use integer ids for team_home and team_guest in a table res instead of full names because it's going to be a huge table. Trying to query both tables:

    SELECT r.tur, r.score, t.team FROM res r, team t WHERE r.team_home=t.team_id or r.team_guest=t.team_id

    But this way or another i can only select "team" once per line. While for the proper table of results (in output) I need to display #tour | team1 | team2 | score. Is there a way to select info from the same collumn twice in one query?

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    A little tweaking on your DDL... this setup should give you good performance with relational integrity.
    Code:
    CREATE TABLE team (
        id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL,
        PRIMARY KEY (id)
    ) TYPE=InnoDB;
    
    CREATE TABLE game (
        id INT NOT NULL AUTO_INCREMENT,
        home_team_id INT NOT NULL REFERENCES team(id),
        guest_team_id INT NOT NULL REFERENCES team(id),
        home_score INT NULL,
        guest_score INT NULL,
        game_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id)
    ) TYPE=InnoDB;
    Then, for your join:

    Code:
    SELECT
        g.*,
        t1.name AS home_team_name,
        t2.name AS guest_team_name
    FROM
        game AS g
        INNER JOIN team AS t1 ON g.home_team_id = t1.id
        INNER JOIN team AS t2 ON g.away_team_id = t2.id;
    Untested, but should work fine (unless I have a bug)....

    G'luck.

  4. #3
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    BTW, I decided to write an article about this on my site... basically just saying the above... I also added an example of how to make a team schedule using UNION:

    http://www.transio.com/articles--tut...lationship.php

  5. #4
    Junior Member
    Join Date
    Dec 2005
    Location
    Vilnius, Lithuania
    Posts
    17
    Member #
    12192
    Hello transio,

    Thank you so much for your advice. Just like you wrote in your tutorial - it might be confusing for those new to db. I haven't tested your code yet, morning here - will do in an hour. But i'm almost sure it'll work fine. It's great to have such help - fast and efficient!

    Thanks again.

  6. #5
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Quote Originally Posted by transio
    Code:
    SELECT
        g.*,
        t1.name AS home_team_name,
        t2.name AS guest_team_name
    FROM
        game AS g
        INNER JOIN team AS t1 ON g.home_team_id = t1.id
        INNER JOIN team AS t2 ON g.away_team_id = t2.id;
    Untested, but should work fine (unless I have a bug)....
    Tested it... there was a bug... "AWAY_TEAM_ID" shoudl be "GUEST_TEAM_ID".
    Code:
    SELECT
        g.*,
        t1.name AS home_team_name,
        t2.name AS guest_team_name
    FROM
        game AS g
        INNER JOIN team AS t1 ON g.home_team_id = t1.id
        INNER JOIN team AS t2 ON g.guest_team_id = t2.id;

  7. #6
    Junior Member
    Join Date
    Dec 2005
    Location
    Vilnius, Lithuania
    Posts
    17
    Member #
    12192
    Yes, it's exactly what I was looking for. I might have questions about implementation later on (how would the query work from php)... will try myself first. But the MySQL part is clear and working on test db.

  8. #7
    Junior Member
    Join Date
    Dec 2005
    Location
    Vilnius, Lithuania
    Posts
    17
    Member #
    12192
    Hello again,

    I'm trying to figure out the following:
    In the table "game" i have a cell "tour". There has to be an option of displaying results by tour. So in a select command:
    WHERE tour=$tour;

    Tours are not always INT but sometimes 1/16, 1/8, etc - VARCHAR
    WHERE tour='$tour'; - works fine.

    The question is - if i'm going to store information about many tournaments for many seasons in the same table - there will be really a lot of info (MAYBE 1000s lines) wouldn't VARCHAR 1/16, 1/8 - for tour slow down the performance considerably compare to INT 1,2,3
    If yes, is it appropriate to make another table for tours and asign them id's (and then join with the main table "game") ?

  9. #8
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    if tournaments involve many games, then there should be a separate table:

    Code:
    CREATE TABLE tournament (
        id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(255) NULL,
        game_count INT NULL,
        team_count InT NULL
        PRIMARY KEY(id)
    ) TYPE=InnoDB;
    Then reference it from the game table (nullable in case the game is not part of a tournament).
    Code:
    CREATE TABLE game (
        id INT NOT NULL AUTO_INCREMENT,
        home_team_id INT NOT NULL REFERENCES team(id),
        guest_team_id INT NOT NULL REFERENCES team(id),
        tournament_id INT NULL REFERENCES tournament(id),
        home_score INT NULL,
        guest_score INT NULL,
        game_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id)
    ) TYPE=InnoDB;
    You can also add a many-to-many table to reference teams playing in a tournament:
    Code:
    CREATE TABLE tournament_team (
        tournament_id INT NOT NULL REFERENCES tournament(id),
        team_id INT NOT NULL REFERENCES team(id),
        PRIMARY KEY (tournament_id, team_id)
    ) TYPE=InnoDB;
    Add one record for each team playing in the tournament.

  10. #9
    Junior Member
    Join Date
    Dec 2005
    Location
    Vilnius, Lithuania
    Posts
    17
    Member #
    12192
    I am trying to use a table for games (to store each game's result for any game of any tournament and season):


    CREATE TABLE `game` (
    `id` int(5) NOT NULL auto_increment,
    `season` varchar(5) NOT NULL,
    `tournament` varchar(5) NOT NULL,
    `date` date NOT NULL,
    `home_team_id` varchar(20) NOT NULL,
    `guest_team_id` varchar(20) NOT NULL,
    `score` varchar(20) NOT NULL,
    `tour` varchar(10) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB ;

    A table for teams as you suggested:

    CREATE TABLE `team` (
    `id` int(11) NOT NULL auto_increment,
    `name` varchar(50) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;

    Tournaments are stored in a similar way like teams:

    CREATE TABLE `tournament` (
    `t_id` int(5) NOT NULL auto_increment,
    `t_name` varchar(50) NOT NULL,
    PRIMARY KEY (`t_id`)
    ) ENGINE=InnoDB ;

    And finally the main table for each tournament/season

    CREATE TABLE `total` (
    `id` int(5) NOT NULL auto_increment,
    `season` int(5) NOT NULL,
    `tournament` int(5) NOT NULL,
    `place` int(5) NOT NULL,
    `team` varchar(50) NOT NULL,
    `total_gms` int(4) NOT NULL,
    `won` int(4) NOT NULL,
    `lost` int(4) NOT NULL,
    `balance` varchar(20) NOT NULL,
    `points` int(5) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB ;

    I also made a table for seasons to replace something like this: 2007/08 with 59
    Everything works fine or almost so on the model page but i'm a bit concerned with productivity of this db. A site owner has an intension to put over 10 000 games in it...

  11. #10
    Junior Member
    Join Date
    Dec 2005
    Location
    Vilnius, Lithuania
    Posts
    17
    Member #
    12192
    Yet one more question - how to sort tours properly to build a menu "game results by tour"? Because i'm getting something like this:
    * 1
    * 1-2
    * 1/2
    * 10
    * 11
    * 12
    * 13
    * 14
    * 15
    * 16
    * 17
    * 18
    * 19
    * 2
    * 20
    * 21
    * 3
    * 3-4
    * 4
    * 5
    * 6
    * 7
    * 8
    * 9
    While the desirable order would be: 1-21, 1/2, 3-4, 1-2

    Thank you for any advice with this.


Page 1 of 3 1 2 3 LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 12:09 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com