MySQL左连接3表错误

Question

我在连接多个表时遇到问题。我可以让 2 个加入，但当我尝试加入所有三个时出现错误:

这有效:

SELECT `capman`.`cman_code`, `capman`.`cman_name`, `caprange`.`cran_name`, `caprange`.`cran_mantextcode` 

from capman, caprange 

LEFT JOIN `capder` ON `caprange`.`cran_code` = `capder`.`cder_rancode` 

AND `capder`.`cder_discontinued` = '0000-00-00 00:00:00' 
LIMIT 10 

这有效:

SELECT `capman`.`cman_code`, `capman`.`cman_name`, `caprange`.`cran_name`, `caprange`.`cran_mantextcode`

from capman

LEFT JOIN `caprange` ON `caprange`.`cran_mantextcode` = `capman`.`cman_code` WHERE LCASE(cman_name) IN ('arbarth','alfa romeo','aston')
LIMIT 10

但是当我尝试将它们像这样组合在一起时:

SELECT `capman`.`cman_code`, `capman`.`cman_name`, `caprange`.`cran_name`, `caprange`.`cran_mantextcode`

from capman, caprange 

LEFT JOIN `caprange` ON `caprange`.`cran_mantextcode` = `capman`.`cman_code` WHERE LCASE(cman_name) IN ('arbarth','alfa romeo','aston')

LEFT JOIN `capder` ON `caprange`.`cran_code` = `capder`.`cder_rancode` AND `capder`.`cder_discontinued` = '0000-00-00 00:00:00'

LIMIT 10

我收到错误:不唯一的表/别名:'caprange'

Answer 1

不确定您是否打算加入 caprange 两次。如果是这样，您可以使用别名来指向正确的表。该查询可以重写为:

SELECT `capman`.`cman_code`, `capman`.`cman_name`, `caprange`.`cran_name`, `caprange`.`cran_mantextcode`
from capman, caprange r1 
LEFT JOIN `caprange` r2 ON r2.`cran_mantextcode` = `capman`.`cman_code` WHERE LCASE(cman_name) IN ('arbarth','alfa romeo','aston')
LEFT JOIN `capder` ON r1.`cran_code` = `capder`.`cder_rancode` AND `capder`.`cder_discontinued` = '0000-00-00 00:00:00'
LIMIT 10

由于我不确定您要使用 caprange 连接哪个表，请将查询中的 r1/r2 替换为正确的别名。