mysql - 使用 join 从数据库中找出国家明智的所有州和州明智的城市

Question

使用 join 从数据库中找出国家/地区的所有州和州/州的城市

我的数据库中有三个表，即国家、州、城市我使用主键和外键在它们之间提供映射。我想要特定的“印度”国家以及相关国家的所有州和城市......我该怎么办？？？在这里，我给出我的代码......我创建的......给我任何其他建议

select countries.name as country,
states.name as state,
cities.name as city
from states inner join cities
on states.id = cities.state_id
inner join countries
WHERE countries.name='India';

Answer 1

请使用以下查询
表格包含以下数据 -

+------+-------+
| id   | name  |
+------+-------+
|    1 | india |
|    2 | pak   |
|    3 | afgan |
+------+-------+

状态表

+------+---------+------------+
| id   | name    | country_id |
+------+---------+------------+
|    1 | haryana |          1 |
|    2 | gujrat  |          1 |
|    3 | himchal |          2 |
+------+---------+------------+

城市表

+------+-------------+----------+
| id   | name        | state_id |
+------+-------------+----------+
|    1 | bahadurgarh |        1 |
|    2 | hisar       |        1 |
|    3 | surat       |        2 |
|    4 | shimla      |        3 |
+------+-------------+----------+




select cities.id , cities.name ,states.id , states.name ,countries.name from cities left join states on cities.state_id = states.id left join countries on states.country_id = countries.id where countries.name='india' ;

+------+-------------+------+---------+-------+
| id   | name        | id   | name    | name  |
+------+-------------+------+---------+-------+
|    1 | bahadurgarh |    1 | haryana | india |
|    2 | hisar       |    1 | haryana | india |
|    3 | surat       |    2 | gujrat  | india |
+------+-------------+------+---------+-------+