-----------------------------------

octopi

Thu Apr 08, 2004 3:48 pm


Baseball

-----------------------------------

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

Thu Apr 08, 2004 5:04 pm



-----------------------------------

For all records that involve games played between two teams

SELECT 
   games.id,
   games.hometeam,
   games.homescore,
   games.awayteam,
   games.awayscore,
   games.numberofinnings
FROM
   games
WHERE
   games.hometeam = 
   AND
   games.awayteam = 
GROUP BY
   games.hometeam;


-----------------------------------

octopi

Thu Apr 08, 2004 5:11 pm



-----------------------------------

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

Thu Apr 08, 2004 5:28 pm



-----------------------------------

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


-----------------------------------

octopi

Thu Apr 08, 2004 5:34 pm



-----------------------------------

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

Thu Apr 08, 2004 8:00 pm



-----------------------------------

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:

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');