Date returning null when no null in table

I'm trying to get the details of a rugby match to show up, and the details I want are the Match ID, Match Date, Match Time, Home Team, Away Team, Home Score, Away Score.

I need the SQL statement to look at the match date to work out the score (as points have changed over the years).

The statement below works fine for all previous scoring structures, but when it gets to the most recent (which currently has a null end date) it breaks.

The issue isn't that it has no end date, as I have it so it sets todays date if the date is null, it is that it loses the date from info, and I have no idea why.

SELECT 
M.MatchID, 
DATE_FORMAT(M.Date, '%d %b %Y'), 
TIME_FORMAT(M.time, '%l:%i %p') AS time,
T1.name, 
T2.name, 
SUM(M.HTries * S.Try) + SUM(M.HConv * S.Conv) + SUM(M.HPT * S.PT) + SUM(M.HPG * S.PG) + SUM(M.HDG * S.DG) AS HScore,
SUM(M.ATries * S.Try) + SUM(M.AConv * S.Conv) + SUM(M.APT * S.PT) + SUM(M.APG * S.PG) + SUM(M.ADG * S.DG) AS AScore,
S.ScoreDateFrom AS ScoreDateFrom,
CASE WHEN NOT S.ScoreDateTo = '0000-00-00' then S.ScoreDateTo ELSE CURDATE() 

END AS ScoreDateTo
FROM 
scoring S, matches M
LEFT JOIN teams T1
    ON T1.TeamID=M.HTeam
LEFT JOIN teams T2
    ON T2.TeamID=M.ATeam
WHERE 
    M.Date >= ScoreDateFrom and M.Date <= ScoreDateTo and M.MatchID = 1;

If I choose Match 1, which is on an old scoring structure, I get all the right info:

+---------+---------------------------------+---------+-------------+-----------+--------+--------+---------------+-------------+                  
| MatchID | DATE_FORMAT(M.Date, '%d %b %Y') | time    | name        | name      | HScore | AScore | ScoreDateFrom | ScoreDateTo |                  
+---------+---------------------------------+---------+-------------+-----------+--------+--------+---------------+-------------+                  
|       1 | 22 Oct 2016                     | 7:35 PM | New Zealand | Australia |     37 |     10 | 2016-05-22    | 2017-07-31  |                  
+---------+---------------------------------+---------+-------------+-----------+--------+--------+---------------+-------------+ 

But if I set for a recent game, I get this:

+---------+---------------------------------+---------+----------+--------+--------+--------+---------------+-------------+                        
| MatchID | DATE_FORMAT(M.Date, '%d %b %Y') | time    | name     | name   | HScore | AScore | ScoreDateFrom | ScoreDateTo |                        
+---------+---------------------------------+---------+----------+--------+--------+--------+---------------+-------------+                        
|      93 | 11 Feb 2018                     | 3:00 PM | Scotland | France |   NULL |   NULL | NULL          | 2018-02-13  |                        
+---------+---------------------------------+---------+----------+--------+--------+--------+---------------+-------------+ 

The table with the scoring details in it looks like this:

+---------+---------------+-------------+------+------+------+------+------+------+---------------------+                                          
| ScoreID | ScoreDateFrom | ScoreDateTo | Try  | PT   | Conv | PG   | DG   | Mark | Update_TS           |                                          
+---------+---------------+-------------+------+------+------+------+------+------+---------------------+                                          
|       1 | 1977-01-01    | 1992-06-30  |    4 |    4 |    2 |    3 |    3 |    0 | 2017-02-08 17:56:39 |                                          
|       2 | 1992-07-01    | 2016-05-13  |    5 |    5 |    2 |    3 |    3 |    0 | 2018-02-13 01:42:29 |                                          
|       3 | 1971-09-01    | 1976-12-31  |    4 |    4 |    2 |    3 |    3 |    3 | NULL                |                                          
|       4 | 2017-08-01    | 0000-00-00  |    5 |    7 |    2 |    3 |    3 |    0 | NULL                |                                          
|       5 | 2016-05-14    | 2016-05-21  |    6 |    8 |    2 |    2 |    3 |    0 | NULL                |                                          
|       6 | 2016-05-22    | 2017-07-31  |    5 |    5 |    2 |    3 |    3 |    0 | NULL                |                                          
+---------+---------------+-------------+------+------+------+------+------+------+---------------------+ 

Appreciate if you can let me know what is going wrong?

Thanks

2 answers

  • answered 2018-02-13 02:20 Ctznkane525

    The way you are joining matches and scoring.

    The match for 11 Feb 2018 has no associated scoring record.

    Thus, when the join occurs, for that match, it will return a NULL for any field in scoring.

  • answered 2018-02-13 02:21 Barmar

    You can't refer to aliases from the SELECT list in the WHERE clause. So WHERE M.Date >= ScoreDateFrom and M.Date <= ScoreDateTo and M.MatchID = 1; uses the value of ScoreDateTo from the actual table, not CURDATE() as adjusted. You need to repeat that adjustment in WHERE.

    WHERE M.Date >= ScoreDateFrom and M.Date <= IF(ScoreDateTo = '0000-00-00', CURDATE(), ScoreDateTo) and M.MatchID = 1;