CODEWITHSUNDEEP
mysqlsearch
Alan A
Alan A

Reputation: 2551

MySQL refinement of query

I'm uing the following query to give me a set of the last 20 matches for a team. I want to find their goals scored in the last 20 matches and order the results by (goals scored, date):

SELECT * FROM (
     SELECT *, `against` AS `goalsF` , `for` AS `goalsA`
     FROM `matches` , `teams` , `outcomes`
     WHERE(
     `home_team_id`=7 AND `matches`.away_team_id = `teams`.team_id
     OR 
     `away_team_id`=7 AND `matches`.home_team_id = `teams`.team_id
     )
     AND `matches`.score_id = `outcomes`.outcome_id
     ORDER BY `date` DESC
     LIMIT 0 , 20
     ) res
ORDER BY `goalsF`

The problem is that:

So what I need to be able to do is something like:

if (`home_team_id`=7 AND `matches`.away_team_id = `teams`.team_id) 
 SELECT *, `for` AS `goalsF` , `against` AS `goalsA`


if (`away_team_id`=7 AND `matches`.home_team_id = `teams`.team_id) 
 SELECT *, `against` AS `goalsF` , `for` AS `goalsA`

But, this must be peformed on the sub-set or results. I'm not sure if this is even possible, but it is beyond my knowledge of MYSQL.

Any help would be hugely appreciated.

Alan.

Upvotes: 0

Views: 118

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

First, you really need to learn ANSI standard join syntax, where you put the join conditions in an on clause rather than a from clause. Also, aliases may a query much more readable.

The following does the logic that you want, although it does not include the team names:

SELECT *
FROM (SELECT *,
             (case when m.home_team_id = 7 then o.against end) as `goalsF` ,
             (case when m.away_team_id = 7 then o.`for` end)  as `goalsA`
      FROM `matches` m join
           `outcomes` o
           on m.score_id = o.outcome_id
      WHERE m.home_team_id = 7 or m.away_team_id = 7
      ORDER BY `date` DESC
      LIMIT 0 , 20
     ) res
ORDER BY `goalsF`

To get the team names, you should join twice to the teams table, once for the home team and once for the away team. You can do this either in the subquery or afterwards. It is also a good idea to explicit mention the columns you are choosing and to include a table alias on every column reference:

SELECT *
FROM (SELECT m.*, o.*,
             homet.team_name as hometeam_name, awayt.team_name as away_team_name,
             (case when m.home_team_id = 7 then o.against end) as `goalsF` ,
             (case when m.away_team_id = 7 then o.`for` end)  as `goalsA`
      FROM `matches` m join
           `outcomes` o
           on m.score_id = o.outcome_id join
           teams homet
           on homet.team_id = m.home_team_id join
           teams awayt
           on awayt.team_id = m.away_team_id
      WHERE m.home_team_id = 7 or m.away_team_id = 7
      ORDER BY `date` DESC
      LIMIT 0 , 20
     ) res 
ORDER BY `goalsF`

EDIT:

To get just goals for team 7, you can use:

             (case when m.home_team_id = 7 then o.`for`
                   when m.away_team_id = 7 then o.against
              end) as goals

To get goals for the other team:

             (case when m.home_team_id = 7 then o.against
                   when m.away_team_id = 7 then o.`for`
              end) as goals

EDIT II:

To get the "other" team name, the logic is similar. Replace the team name references in the select with:

             (case when m.home_team_id = 7 then awayt.team_name
                   when m.away_team_id = 7 then homet.team_name
              end) as goals

Upvotes: 2

Related Questions