Query help please

--0016363b852cbc6d740490e95513
Content-Type: text/plain; charset=ISO-8859-1

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

--0016363b852cbc6d740490e95513--

Fwd: Query help please

--0015175cb4a484479a0490eb066d
Content-Type: text/plain; charset=ISO-8859-1

I wondered if anyone can help me ? Do you need any further information ?

Cheers
Neil

---------- Forwarded message ----------
From: Tompkins Neil
Date: Thu, Sep 23, 2010 at 9:49 AM
Subject: Query help please
To: "[MySQL]"


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

--0015175cb4a484479a0490eb066d--