I wondered if anyone can help me ? Do you need any further information ? Cheers Neil
---------- Forwarded message ---------- From: Tompkins Neil <neil.tompk...@googlemail.com> Date: Thu, Sep 23, 2010 at 9:49 AM Subject: Query help please To: "[MySQL]" <mysql@lists.mysql.com> Hi all, I've the following query : SELECT fixtures_results.seasons_id , home_teams_id AS teams_id , 1 AS home ,0 AS away , (SELECT SUM(goals) FROM players_appearances WHERE fixtures_results.fixtures_results_id = players_appearances.fixtures_results_id AND players_appearances.teams_id = home_teams_id) AS home_goals_aa, IF(home_goals > away_goals, 1, 0) AS won_home , IF(home_goals = away_goals, 1, 0) AS drawn_home , IF(home_goals < away_goals, 1, 0) AS lost_home , home_goals AS scored_home , away_goals AS conceded_home , 0 AS won_away , 0 AS drawn_away , 0 AS lost_away , 0 AS scored_away , 0 AS conceded_away FROM fixtures_results WHERE fixtures_results.competitions_id = 1 AND fixtures_results.seasons_id = 1 AND fixtures_results.status = 'approved' Basically I have a table called player_appearances which contains a SUM of goals for each fixture for the home and away team. How can I use this SUM called "home_goals_aa", in my logic like IF(home_goals > away_goals, 1, 0) AS won_home , IF(home_goals = away_goals, 1, 0) AS drawn_home , IF(home_goals < away_goals, 1, 0) AS lost_home , Cheers Neil
