首页 文章

创建两个连接到同一个表

提问于
浏览 次
0

从预定我需要两个连接到国家，两个连接到州和两个连接到城市 .

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";

谢谢

php sql join

2 回答

  • 0

    在连接表上使用别名来分隔重复的表连接：

    $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
    回复于
  • 0

    试试这个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";
    回复于

相关问题