Jim Fell Jim Fell - 5 months ago 12
MySQL Question

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:

Error Code: 1054. Unknown column 'esjp_layout.section_id' in 'on clause'


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. I know this query is somewhat wordy, but it's being assembled programatically, so character count doesn't concern me.

Answer

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

Instead:

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.