mysql执行left join时报错Unknown column 'xxx' in 'on clause'

最新推荐文章于 2024-08-02 19:47:31 发布

纯白

最新推荐文章于 2024-08-02 19:47:31 发布

阅读量3k

点赞数 2

分类专栏：学习笔记文章标签： mysql sql join查询

本文链接：https://blog.csdn.net/qq_25627105/article/details/99625173

版权

学习笔记专栏收录该内容

6 篇文章 0 订阅

订阅专栏

mysql执行left join时报错Unknown column 'xxx' in 'on clause'

遇到的问题
解决方法

遇到的问题

执行如下sql时

select 
    ad.answer_id,ev.title,ev.`year`,ev.suitable,ac.user_name,
    jt.job_name,po.position_name,ad.score,ad.`level` 
from auditing ad,account ac,answer an,evaluation ev 
left join job_title jt on ac.job_title_id = jt.id 
left join position po on ac.position_id = po.id 
where ad.answer_id = an.id and an.evaluation_id = ev.id 
    and an.aid = ac.aid and ac.dept_id = 1 
    and ev.`year` = 1 and ad.audit_type = 1

报错
在这里插入图片描述

解决方法

把from后的多个表用括号括起来

select 
    ad.answer_id,ev.title,ev.`year`,ev.suitable,ac.user_name,
    jt.job_name,po.position_name,ad.score,ad.`level` 
from (auditing ad,account ac,answer an,evaluation ev) 
left join job_title jt on ac.job_title_id = jt.id 
left join position po on ac.position_id = po.id 
where ad.answer_id = an.id and an.evaluation_id = ev.id 
    and an.aid = ac.aid and ac.dept_id = 1 
    and ev.`year` = 1 and ad.audit_type = 1