mysql - sql查询语句问题

Question

有两张表

tbl_user(uid,uname,province_id,city_id,area_id)
tbl_area(id,aname,pid)

其中province_id,city_id,area_id全部为tbl_area表的主键id
能不能通过一条sql语句查询出如下结果

uid,uname,province_name,city_name,area_name 
 1,  张三,     河北,       衡水,      饶阳

Answer 1

SELECT u.uid, u.uname, p.aname AS province_name, c.aname AS city_name, a.aname AS area_name
FROM tbl_user AS u
LEFT JOIN tbl_area AS p ON p.id = u.province_id
LEFT JOIN tbl_area AS c ON c.id = u.city_id
LEFT JOIN tbl_area AS a ON a.id = u.area_id
LIMIT 100

Answer 2

Are you sure that the above information can be queried through only 2 tables?
The three fields province_name, city_name and area_name do not appear!

Answer 3

The answer is as @黄红 said.
But a better design should be to distinguish three tables, because provinces, cities, and counties are basically constant tables and will basically not change. It would be better to store them in different tables.