List:General Discussion« Previous MessageNext Message »
From:Tompkins Neil Date:September 23 2010 10:50am
Subject:Fwd: Query help please
View as plain text  
I wondered if anyone can help me ?  Do you need any further information ?

Cheers
Neil

---------- Forwarded message ----------
From: Tompkins Neil <neil.tompkins@stripped>
Date: Thu, Sep 23, 2010 at 9:49 AM
Subject: Query help please
To: "[MySQL]" <mysql@stripped>


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

Thread
Query help pleaseTompkins Neil23 Sep
  • Fwd: Query help pleaseTompkins Neil23 Sep