从Scheduled我需要做两个连接到国家，两个连接到州和两个连接到城市。
My Tables
Schedule
Destination Country ID
Destination City ID
Destination State ID
Current Country ID
Current City ID
Current State ID
-------------------
Country
ID
Country
-------------------
State
ID
Statename
Countryid
-------------------
City
ID
City
Stateid
Countryid
我需要从计划表中读取数据，并使用国家、州和城市名称列出它，这些名称存储在国家、州和城市表中，并通过ID字段链接。我有第一个连接到每个表工作，但当我试图第二个查询失败说"Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given"。
什么作品…
$query="SELECT schedule.username, schedule.ret, country.country, state.statename, city.city, schedule.business, schedule.pleasure
FROM schedule
JOIN country
ON schedule.dest_country=country.id
JOIN state
ON schedule.dest_state=state.id
JOIN city
ON schedule.dest_city=city.id";
什么不…
$query="SELECT schedule.username, schedule.ret, country.country, state.statename, city.city, schedule.business, schedule.pleasure, country.country
FROM schedule
JOIN country
ON schedule.dest_country=country.id
JOIN state
ON schedule.dest_state=state.id
JOIN city
ON schedule.dest_city=city.id
JOIN country
ON schedule.cur_country=country.id";
谢谢
在连接表上使用别名来分隔重复的表连接:
$query="SELECT schedule.username, schedule.ret, destcountry.country, state.statename, city.city, schedule.business, schedule.pleasure, curcountry.country
FROM schedule
JOIN country destcountry
ON schedule.dest_country=destcountry.id
JOIN state
ON schedule.dest_state=state.id
JOIN city
ON schedule.dest_city=city.id
JOIN country curcountry
ON schedule.cur_country=curcountry.id";
为了明确结果集，您可以将结果列命名为:
$query="SELECT schedule.username, schedule.ret, destcountry.country as destcountry, state.statename, city.city, schedule.business, schedule.pleasure, curcountry.country as currentcountry
试试这个SQL兄弟!
$query="SELECT * FROM schedule SC
LEFT JOIN country C ON C.id = SC.dest_country
LEFT JOIN state ST ON ST.id = SC.dest_state
LEFT JOIN city Ci ON Ci.id = SC.dest_city
LEFT JOIN country C2 ON C2.id = SC.cur_country";