Baseball

[octopi]

I have data in a database (imagine that)

It contains a list of games
id, date, hometeam, homescore, awayteam, awayscore, numberofinnings

I want to do the following:
I have a hometeam, and an away team.

(C is any other team)
I want to know like
home vs C (home won)
away vs C (away lost)

I want to find all the games, that have games in common given two team names. So I can then say home should beat away.


Can anyone suggest an easy way to do this, I'm using a mysql db, and php, but I'm just looking for a general method to do this.

[wtd]

For all records that involve games played between two teams

code:

SELECT    games.id,    games.hometeam,    games.homescore,    games.awayteam,    games.awayscore,    games.numberofinnings FROM    games WHERE    games.hometeam = <insert home team name or id here>    AND    games.awayteam = <insert away team name or id here> GROUP BY    games.hometeam;

[octopi]

yes, I have that....but I need to find rows that have a game in common.

Team A played Team C
Team C played Team B
Team D played Team B


If I tell the computer Team A, and Team B, it will return those two results. (ones where team A has played C, and team B has played C, but not games where a team played a team which the other hasn't)


What I want to do is given two team names, find all games in which the teams have played other teams, and then used that to compare team a, and team b, based on previous wins against other teams, when they share a common team c.

[wtd]

Ah... this may require some considerable logic outside of your database query. It's gonna get messy.

[octopi]

Ya.....any ideas?

I'm starting by taking all the results where team a, or team b are a participating team in the game.
Then I'm going to sort the teams alphabetically based on the team whos not A, or B, and then somehow compare them.

I need ideas...

[wtd]

octopi wrote:

Ya.....any ideas?

I'm starting by taking all the results where team a, or team b are a participating team in the game.
Then I'm going to sort the teams alphabetically based on the team whos not A, or B, and then somehow compare them.

I need ideas...

Well... all of the games participated in by either team, but not by both teams:

code:

SELECT    g.id, g.hometeam, g.homescore,    g.awayteam, g.awayscore, g.numberofinnings FROM    games g WHERE    (g.hometeam = 'team A' AND g.awayteam <> 'team B')    OR    (g.hometeam = 'team B' AND g.awayteam <> 'team A')    OR    (g.awayteam = 'team A' AND g.hometeam <> 'team B')    OR    (g.awayteam = 'team B' AND g.hometeam <> 'team A');