MySQL table joins

[mmdesign]

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?

[smoseley]

A little tweaking on your DDL... this setup should give you good performance with relational integrity.

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:

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.

[smoseley]

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

[mmdesign]

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.

[smoseley]

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".

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;

[mmdesign]

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.

[mmdesign]

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") ?

[smoseley]

if tournaments involve many games, then there should be a separate table:

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

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:

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.

[mmdesign]

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...

[mmdesign]

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.