使用join从数据库中查找country wise所有州和州wise城市

Question

使用join从数据库中查找country wise所有州和州wise城市

我在数据库中有三个表，即国家，州，城市，我使用主键和外键给出了它们之间的映射。我想要具体的“印度”国家和相关的国家，所有的邦和cities...How我能做吗？在这里我给我的code...which I‘m created..Give me任何其他建议

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

Answer 2

从sql中找出country wise state和state wise city不是一个好主意。

你应该用javascript试试这个just include this file in your required webpage

Answer 3

尝试以下操作(很抱歉代码中没有换行符。不知道它是如何工作的)。

Select countries.name as country, states.name as sta from countries left join cities on states.id= cities.state_id where states.country_id in (SELECT countries.id FROM countries where countries.name='India')