Using mysql to determin win or loss

[nipster2]

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

[nipster2]

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