Problem with CASE/CONCAT

[frasernm]

Hi,

I have a query as follows (I've removed most of the selected fields for clarity):

SELECT fixid,
CASE WHEN result IS NULL THEN ' '
WHEN result = 'won' or result = 'lost' THEN CONCAT(result, ' by ', margin, ' ', resulttype)
ELSE result END AS res,
result, margin, resulttype
FROM team as ownt, team as oppt, fixture, season, competition
LEFT JOIN matchdetails ON fixid = matchfixid
WHERE seasonid = fixseason
AND ownt.teamID = ownteamid
AND oppt.teamID = oppteamid
AND compid = fixcompid
ORDER BY fixdate ASC, compname ASC

I've narrowed the problem to being with the CASE statement.

As above it produces the follwing (bizarre) results:

| 131 | NULL | NULL | NULL | NULL |
| 132 | NULL | NULL | NULL | NULL |
| 1 | lost by 49 runs | lost | 49 | runs |
| 2 | NULL | lost | 43 | runs |
| 3 | NULL | lost | 86 | runs |
| 4 | NULL | lost | 6 | wickets |
| 5 | won by 26 runs | won | 26 | runs |
| 6 | NULL | drawn | 0 | |
| 7 | abandoned | abandoned | 0 | |
| 12 | NULL | lost | 39 | runs |
| 16 | NULL | cancelled | 0 | |
| 8 | NULL | drawn | 0 | |
| 9 | won by 63 runs | won | 63 | runs |
| 15 | NULL | cancelled | 0 | |
| 14 | cancelled | cancelled | 0 | |

If I replace the CONCAT section with 2 I get

| 131 |   | NULL | NULL | NULL |
| 132 |   | NULL | NULL | NULL |
| 1 | 2 | lost | 49 | runs |
| 2 | 2 | lost | 43 | runs |
| 3 | 2 | lost | 86 | runs |
| 4 | 2 | lost | 6 | wickets |
| 5 | 2 | won | 26 | runs |
| 6 | drawn | drawn | 0 | |
| 7 | abandoned | abandoned | 0 | |
| 12 | 2 | lost | 39 | runs |
| 16 | cancelled | cancelled | 0 | |
| 8 | drawn | drawn | 0 | |
| 9 | 2 | won | 63 | runs |
| 15 | cancelled | cancelled | 0 | |
| 14 | cancelled | cancelled | 0 | |

mysql> describe matchdetails;
+-----------------+----------------------------------------------------+------+-----+--------+-------+
| Field | Type | Null | Key | Default| Extra |
+-----------------+----------------------------------------------------+------+-----+--------+-------+
| MatchFixID | smallint(5) | | PRI | 0 | |
| TossWonBy | smallint(5) | YES | | NULL | |
| WhoElectedTo | enum('Bat','Field') | YES | | NULL | |
| result | enum('won','drawn','lost','cancelled','abandoned') | YES | | NULL | |
| Margin | smallint(5) | YES | | NULL | |
| TargetToWin | smallint(3) | YES | | NULL | |
| TargetTime | smallint(3) | YES | | NULL | |
| TargetOvers | smallint(3) | YES | | NULL | |
| TargetTimeType | enum('Days','Hours','Minutes') | YES | | NULL | |
| TargetTimeIsMin | enum('y','n') | YES | | NULL | |
| Report | text | YES | | NULL | |
| resulttype | varchar(100) | YES | | NULL | |
+-----------------+----------------------------------------------------+------+-----+--------+-------+

Any idea why the first query is going astray?

Many thanks in advance,

Fraser