mysql - 从 2 个不同的数据库连接时出现未知列错误

Question

我在构建查询时遇到问题，在 on 子句中的未知列 j.id 上遇到错误。

这是我到目前为止的查询

SELECT 
    j.id,
    j.title,
    venues.name as venueName,
    ja.completed
FROM
    dbsivcmsnew.jobs AS j, 
    venues
LEFT JOIN dbsiv.job_applications AS ja ON ja.jobId = j.id
WHERE 
    j.venueId = venues.id 
    AND j.closingDate > UNIX_TIMESTAMP()
    AND j.active = 1
ORDER BY 
    j.closingDate DESC

从here我可以看到语法正确。

但是，JOIN 可能是不必要的，我的查询需要返回的是

SELECT 
    j.id,
    j.title,
    venues.name as venueName,
    ja.completed
FROM
    jobs AS j, 
    venues,
    dbsiv.job_applications AS ja
WHERE 
    j.venueId = venues.id 
    AND j.closingDate > UNIX_TIMESTAMP()
    AND j.active = 1
    AND ja.pin = $CurrentUsersId //This needs to optional though, if no match ja.completed should be 0
ORDER BY 
    j.closingDate DESC

是否可以在不使用连接的情况下使 WHERE 语句可选？

Answer 1

嗯，你混合了“表连接”的语法，这......不是一个好主意

只需在 LEFT JOIN 中添加附加子句即可解决问题

SELECT 
    j.id,
    j.title,
    v.name as venueName,
    ja.completed
FROM
    jobs j
inner join venues v on v.id = j.venueID
left join dbsiv.job_applications ja on ja.jobId = j.id and ja.pin = $CurrentUsersId
WHERE 

    AND j.closingDate > UNIX_TIMESTAMP()
    AND j.active = 1
ORDER BY 
    j.closingDate DESC