Programming C, C++, Java, PHP, Ruby, Turing, VB
Computer Science Canada 
Programming C, C++, Java, PHP, Ruby, Turing, VB  

Username:   Password: 
 RegisterRegister   
 Baseball
Index -> General Programming
View previous topic Printable versionDownload TopicSubscribe to this topicPrivate MessagesRefresh page View next topic
Author Message
octopi




PostPosted: Thu Apr 08, 2004 3:48 pm   Post subject: 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.
Sponsor
Sponsor
Sponsor
sponsor
wtd




PostPosted: Thu Apr 08, 2004 5:04 pm   Post subject: (No subject)

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




PostPosted: Thu Apr 08, 2004 5:11 pm   Post subject: (No subject)

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




PostPosted: Thu Apr 08, 2004 5:28 pm   Post subject: (No subject)

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




PostPosted: Thu Apr 08, 2004 5:34 pm   Post subject: (No subject)

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




PostPosted: Thu Apr 08, 2004 8:00 pm   Post subject: (No subject)

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');
Display posts from previous:   
   Index -> General Programming
View previous topic Tell A FriendPrintable versionDownload TopicSubscribe to this topicPrivate MessagesRefresh page View next topic

Page 1 of 1  [ 6 Posts ]
Jump to:   


Style:  
Search: