php - Getting only one result for a "football matchup" per team per week

Question

I'm putting together a new project, and one of the things I need to do is get the results of a Fake Football (American) match. I'm doing this in php and mysql, utilizing CodeIgniter as my framework. My dbfiddle is as follows:

https://www.db-fiddle.com/f/5Q7QezddpNEGabaia2w5FQ/0

Now, as you can see, each team has an entry for home_team_id and away_team_id. What I would like to do is have a query that gets only ONE result for each team (regardless if they are home or away). I'd rather not programmatically process this data if I don't have to.

The results should be something along the lines of the following:

SELECT week, home_team_id, away_team_id, 
    my_score, their_score, 
    a.team_name as home_team, b.team_name as away_team 
FROM nfl_user_matchups nm 
LEFT JOIN user_teams a ON nm.home_team_id = a.user_teams_id 
LEFT JOIN user_teams b ON nm.away_team_id = b.user_teams_id 
WHERE week = 1

Expected Final Data would be something like this (my_score would be home_team_id score, their_score would be away_team_id score):

+------+--------------+--------------+----------+-------------+
| week | home_team_id | away_team_id | my_score | their_score |
+------+--------------+--------------+----------+-------------+
| 1    | 3            | 9            | 112      | 144         |
+------+--------------+--------------+----------+-------------+
| 1    | 7            | 2            | 85       | 96          |
+------+--------------+--------------+----------+-------------+
| 1    | 1            | 6            | 111      | 114         |
+------+--------------+--------------+----------+-------------+
| 1    | 4            | 5            | 99       | 125         |
+------+--------------+--------------+----------+-------------+
| 1    | 8            | 10           | 140      | 122         |
+------+--------------+--------------+----------+-------------+

标签： phpmysqlcodeigniter

Answer 1

Your problem statement basically means that a pair of teams competing, viz., (1,2) are to be treated same as (2,1). One way to handle such requirements is to ensure that for any match between these two teams, we ensure that they are in the same order. So, basically we get the Least() team id value out of the two teams, and always put it in the first index; and the Greatest() value always at the second (last) index. It is noteworthy that Greatest() is not same as Max(). Max() function computes maximum on a column; while Greatest() is generally used to compare values across a row.

Now, we can simply GROUP BY on this pair and compute the aggregates as desired. While determining home/away scores, you will need to use CASE .. WHEN expression to determine whether a particular row has home id the least, or away id:

SELECT 
  week, 
  LEAST(home_team_id, away_team_id) AS home_id, 
  GREATEST(home_team_id, away_team_id) AS away_id, 
  MAX(CASE 
      WHEN LEAST(home_team_id, away_team_id) = home_team_id 
      THEN my_score 
      ELSE their_score
      END) AS home_score,
  MAX(CASE 
      WHEN GREATEST(home_team_id, away_team_id) = away_team_id 
      THEN their_score 
      ELSE my_score
      END) AS away_score 
FROM nfl_user_matchups 
WHERE week = 1
GROUP BY
  week, 
  home_id, 
  away_id;

View on DB Fiddle