同一个表SQL中的左连接

Question

SELECT 

`_conf_cities`.`cityid`,
`_conf_cities`.`countryid`,
`_conf_cities`.`name_mkd`,
`_conf_cities`.`child_municipality`,
`_conf_cities`.`parent`,

`conf_countries`.`countryid`,
`conf_countries`.`alpha2`

FROM `_conf_cities`

LEFT JOIN `conf_countries` ON `_conf_cities`.`countryid` = `conf_countries`.`countryid`

LEFT JOIN `_conf_cities` ON  `_conf_cities`.`cityid` = `_conf_cities`.`parent`

WHERE `_conf_cities`.`child_municipality` = '0'

当我尝试对_conf_cities本身执行LEFT JOIN操作时，出现了问题。

还能用另一种方式来完成吗？

Answer 1

你必须使用别名，比如：

SELECT 
_conf_cities.cityid,
_conf_cities.countryid,
_conf_cities.name_mkd,
_conf_cities.child_municipality,
_conf_cities.parent,
conf_countries.countryid,
conf_countries.alpha2,
t2.cityid -- Now you can use alias
FROM _conf_cities
LEFT JOIN conf_countries ON _conf_cities.countryid = conf_countries.countryid
LEFT JOIN _conf_cities AS t2 ON  _conf_cities.cityid = t2.parent
WHERE
    _conf_cities.child_municipality = '0'
    AND t2.child_municipality = '0' -- Also you can use alias here