Why doesn't all_weather = 1 for race_type = 'All Weather Flat' in SmartForm Racing Database?

Question

When I run the following two queries on the SmartForm Racing Database, I get the table of results below.  Why doesn't all_weather = 1 for race_type = 'All Weather Flat'?  Is one of these fields more reliable than the other or is there some other reason why an 'All Weather Flat' race would have all_weather != 1.

SELECT a.race_id

INTO

#sample_races

FROM

(SELECT *

FROM

historic_races

WHERE

course IN ('Kempton', 'Lingfield', 'Southwell', 'Wolverhampton')

AND distance_yards BETWEEN 5 * 220 AND 12 * 220

) a

INNER JOIN historic_runners b

ON a.race_id = b.race_id

GROUP BY

a.race_id

HAVING

min(b.age) >= 0;

 

SELECT course

, race_type

, all_weather

, count(b.race_id)

FROM

historic_races a

INNER JOIN #sample_races b

ON a.race_id = b.race_id

GROUP BY

course

  , race_type

  , all_weather

ORDER BY

course

  , race_type

  , all_weather;

 

course, race_type, all_weather, count(*)

Kempton All Weather Flat 0 223

Kempton All Weather Flat 1 1842

Kempton Flat 0 234

Kempton Flat 1 21

Lingfield All Weather Flat 0 247

Lingfield All Weather Flat 1 3574

Lingfield Flat 0 507

Lingfield Flat 1 53

Southwell All Weather Flat NULL 1

Southwell All Weather Flat 0 1336

Southwell All Weather Flat 1 1453

Southwell Flat 0 84

Southwell Flat 1 17

Wolverhampton All Weather Flat NULL 5

Wolverhampton All Weather Flat 0 441

Wolverhampton All Weather Flat 1 3711

Wolverhampton Flat 0 10

Wolverhampton Flat 1 11

Answer 1

Hi - All Weather should be race_type_id 15.  Try the following query:

mysql> select distinct race_type, race_type_id from historic_races;
+-------------------------+------------------+
| race_type               | race_type_id |
+-------------------------+------------------+
| Flat                         |                 12 |
| Hurdle                    |                 14 |
| Chase                    |                 13 |
| National Hunt Flat  |                   1 |
| All Weather Flat     |                 15 |
| Point to Point         |                   4 |
| Trotting                   |                 17 |
+-------------------------+-----------------+
7 rows in set (1.18 sec)