Jump to content


Photo

Using mysql to determin win or loss


  • Please log in to reply
1 reply to this topic

#1 nipster2

nipster2
  • New Members
  • Pip
  • Newbie
  • 2 posts
  • LocationVancouver, B.C

Posted 28 January 2003 - 07:23 AM

Hi,

I have 2 tables, i know how to join them what im doing is a bit more complex, first table has 2 columns team_id and team_name second table has 4 hometeam_id, awayteam_id, home_score, and away_score is there a select statement where i can determine if a team wins or losses without adding extra colums for who won and who lost, something like:

SELECT team_name, COUNT(home_score>away_score) as win FROM scores, teams WHERE hometeam_id=team_id;

I though this would show when the home team won but if the home team losses it still shows the home team.

hope Im clear and thanks alot in advance
out [br]NiPsTeR

#2 nipster2

nipster2
  • New Members
  • Pip
  • Newbie
  • 2 posts
  • LocationVancouver, B.C

Posted 28 January 2003 - 08:27 AM

never mind you guys are too slow i figured it out .... if anyone needs anything like this in the future this is what you do

SELECT team_name, sum((home_score>away_score and hometeam_id=team_id) or (away_score>home_score and awayteam_id=team_id) as wins, sum((home_score>away_score and awayteam_id=team_id) or (away_score>home_score and hometeam_id=team_id) as losses FROM teams, scores ORDER BY team_name;

and that should do it, i love it when i slove my own problems, just wish it happened more often lol
out [br]NiPsTeR




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users