a single table complex query

[heraldic2]

I manage a fantasy football league.

I have a table in a database that contains a record of every game played over 6 years of the league.

What I would like to do is create a query that I can use to display on a website by php.

I would like to use a drop down list that contains the names of the teams, then when the submit button is pushed the query will take the team name that was chosen and show that teams win / lose record vs. all other teams in the league.

 

Example output:

Green Bay Packers

Team Name

Wins

Losses

Chicago Bears

4

2

Dallas Cowboys

2

12

and so on . . .

In this example the Green Bay Packers have beaten the Chicago Bears 4 of the 6 times they have played and the Packers have lost 2 of the 6 times they have played.

 

I am unsure how to approach this situation as my design of the table has left me with a problem. 

I can compute a teams home wins / losses OR I can compute a teams away wins / losses, but not both at the same time.

 

I use a 1 in the 'home_team_win' column if the home team won.  If the home team lost then they get a 1 in the `home_team_loss`column.  Same holds true for the away team.

 

I just don't know how to get a query that will show me the wins / losses added up over the years for every team that the selected team has played against.

 

Any help with this query would be much appreciated.

 

The table

TABLE `games` (
`games_id` INT(20) NOT NULL AUTO_INCREMENT,
`week` VARCHAR(7) NULL DEFAULT NULL,
`year` YEAR(4) NULL DEFAULT '2011',
`home_owner_id` INT(2) NULL DEFAULT NULL,
`home_team` VARCHAR(50) NULL DEFAULT NULL,
`home_team_score` FLOAT(6,1) NULL DEFAULT NULL,
`home_team_win` INT(1) NULL DEFAULT '0' COMMENT '1 = game winner',
`home_team_loss` INT(1) NULL DEFAULT '0' COMMENT '1 = game loser',
`away_owner_id` INT(2) NULL DEFAULT NULL,
`away_team` VARCHAR(50) NULL DEFAULT NULL,
`away_team_score` FLOAT(6,1) NULL DEFAULT NULL,
`away_team_win` INT(1) NULL DEFAULT '0' COMMENT '1 = game winner',
`away_team_loss` INT(1) NULL DEFAULT '0' COMMENT '1 = game loser',
`playoff_game` INT(1) NULL DEFAULT '0' COMMENT 'Was this game a playoff game? 1 = yes 0 = no',
`superbowl` INT(1) NULL DEFAULT '0' COMMENT 'Was this game a super bowl 1 = yes 0 = no',

 

Thank you very much for your time.

[fenway]

Well, yes, that design is far from ideal.

 

You can just UNION the results.

[heraldic2]

Thank you for your reply.  I will look into using the UNION on the results, however may I also ask what a better table design would be?

 

Thank you for your time

[fenway]

I just mean that if a simple query like this is crazy, then the design needs to be re-worked.  I didn't have a specific recommendation, but I can think of a few if you're interested.

[heraldic2]

I would be very interested in whatever that will point me in the right direction.

[fenway]

Well, if separate games from teams, you won't need a union.