组合查询
表A(存储国家名称):id c_name
1000 中国
1001 美国
表B(存储省/直辖市和地级市和县级市):id city_name parent_id
1000 北京(省/直辖市) 0
1001 天津
.... ....
1009 浙江
1010 杭州(地级市,属于1009浙江) 1009
1011 宁波 1009
.... .... ....
1100 慈溪 1011表C(存储数据):company country_id prov_city city_city city_city_city
北京公司 1000 1009 1011 1100组合查询C表的结果:company country_id prov_city city_city city_city_city
北京公司 中国 浙江 宁波 慈溪请问本条语句应该如何写,3表的组合查询?
表A(存储国家名称):id c_name
1000 中国
1001 美国
表B(存储省/直辖市和地级市和县级市):id city_name parent_id
1000 北京(省/直辖市) 0
1001 天津
.... ....
1009 浙江
1010 杭州(地级市,属于1009浙江) 1009
1011 宁波 1009
.... .... ....
1100 慈溪 1011表C(存储数据):company country_id prov_city city_city city_city_city
北京公司 1000 1009 1011 1100组合查询C表的结果:company country_id prov_city city_city city_city_city
北京公司 中国 浙江 宁波 慈溪请问本条语句应该如何写,3表的组合查询?
a.c_name as country_id ,
b.city_name as prov_city,
d.city_name as city_city,
e.city_name as city_city_city
FROM tableC as c
LEFT JOIN tableA as a on a.id = c.country_id
LEFT JOIN tableB as b on b.id = c.prov_city
LEFT JOIN tableB as d on d.id = c.city_city
LEFT JOIN tableB as e on e.id = c.city_city_city