Using mysql to determin win or loss

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:




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

This will give you what you want...

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