> It turns out that in the address table, there is no city_id = 313 which is London, Ontario
Arguably, for clarity, city.city should be named city.city_name, and neither Canadian provinces nor England ought to be called 'districts'...
select * from city where city_id between 312 and 313;
+---------+--------+------------+---------------------+
| city_id | city | country_id | last_update |
+---------+--------+------------+---------------------+
| 312 | London | 102 | 2006-02-15 04:45:25 |
| 313 | London | 20 | 2006-02-15 04:45:25 |
+---------+--------+------------+---------------------+
select a.*, b.district, c.country
from city a
join address b on a.city_id=b.city_id
join country c on a.country_id=c.country_id
where a.city = 'london';
+---------+--------+------------+---------------------+----------+----------------+
| city_id | city | country_id | last_update | district | country |
+---------+--------+------------+---------------------+----------+----------------+
| 312 | London | 102 | 2006-02-15 04:45:25 | England | United Kingdom |
| 312 | London | 102 | 2006-02-15 04:45:25 | Ontario | United Kingdom |
+---------+--------+------------+---------------------+----------+----------------+
It might be a useful sql learning exercise to write up all the sakila inconsistencies you can find, and post them here.
Edited 1 time(s). Last edit at 04/08/2015 12:01AM by Peter Brawley.