I've downloaded the Worlds-Country-State-and-City-Mysql-Database project from GitHub, it gives a database of countries, states, and cities.
Normally such databases are found in three tables for country, state, and city, but here it is only a single table, and it has all three pieces of data. It works fine but I can't understand how it works.
Here is snapshot of that table.
Somebody please explain this
分享 What you have here is a parent-child relationship. The parent_ID column designates if a location belongs to another(is the child of another) Location_id.
For example to get the direct children (states) of Aruba, you would do a self join as such:
select * from mytable A
left join mytable B -- join same table
on b.parent_id=a.location_id -- with parent of the secondary being equal to the ID of the primary.
where a.location_id='Aruba'
If you want to get the children of the states(cities) , you would need one more self-join
分享
