MySQL Forums
Forum List  »  Newbie

Re: Sakila giving some strange results
Posted by: Peter Brawley
Date: April 08, 2015 12:01AM

> 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.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.