MySQL 错误帮助:On 子句中的未知列 [英] MySQL Error Help: Unknown Column in On Clause

问题描述

我正在尝试组合一个从多个表中提取数据的查询，但出现一个奇怪的错误:

I'm trying to put together a query that pulls data from multiple tables, but I'm getting an odd error:

错误代码:1054.'on 子句'中的未知列'esjp_layout.section_id'

引用的表和列肯定存在，并且拼写正确.这是我正在处理的查询.任何想法出了什么问题?

The table and column referenced certainly exist, and it's spelled correctly. This is the query I'm working with. Any ideas what is wrong?

SELECT
    esjp_section_refs.section_label,
    esjp_content.primary_key, esjp_content.content, esjp_content.summary_id, 
    esjp_role_refs.role_label, 
    esjp_users.first_name, esjp_users.last_name,
    FROM_UNIXTIME(esjp_content.sys_time)
FROM esjp_content
    INNER JOIN esjp_section_refs ON esjp_layout.section_id = esjp_section_refs.primary_key
    INNER JOIN esjp_layout ON esjp_content.primary_key = esjp_layout.content_id 
    INNER JOIN esjp_role_refs ON esjp_content.role_ref = esjp_role_refs.primary_key
    INNER JOIN esjp_users ON esjp_content.author_id = esjp_users.primary_key
WHERE esjp_layout.primary_key = 1
ORDER BY esjp_layout.section_id ASC, esjp_layout.position ASC ;

P.S.我知道这个查询有点冗长，但它是以编程方式组装的，所以我不关心字符数.

推荐答案

问题是在您的 ON 子句中加入的表尚未出现在查询中.

The problem is that the table that is being joined in your ON clause isn't in the query yet.

相反:

SELECT
    esjp_section_refs.section_label,
    esjp_content.primary_key, esjp_content.content, esjp_content.summary_id, 
    esjp_role_refs.role_label, 
    esjp_users.first_name, esjp_users.last_name,
    FROM_UNIXTIME(esjp_content.sys_time)
FROM esjp_content
    INNER JOIN esjp_layout ON esjp_content.primary_key = esjp_layout.content_id 
    INNER JOIN esjp_section_refs ON esjp_layout.section_id = esjp_section_refs.primary_key    
    INNER JOIN esjp_role_refs ON esjp_content.role_ref = esjp_role_refs.primary_key
    INNER JOIN esjp_users ON esjp_content.author_id = esjp_users.primary_key
WHERE esjp_layout.primary_key = 1
ORDER BY esjp_layout.section_id ASC, esjp_layout.position ASC ;

换句话说，您必须通过将表连接到已在查询中的表来连接表.可以这么说，它们必须按顺序加入.

In other words you have to join in a table by joining it to a table that is already in your query. They have to be joined in order, so to speak.

这篇关于MySQL 错误帮助:On 子句中的未知列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！