Home Unknown column in 'on clause' error
Reply: 2

Unknown column in 'on clause' error

rowdyruckus
1#
rowdyruckus Published in 2018-01-12 22:11:28Z

So I'm trying to query three tables and can do two of three but not the third, I've tried several ways and keep getting this error:

#1054 - Unknown column 'thirdDATA.creator_id' in 'on clause'

This works:

SELECT * 
FROM `mainDATA` 
LEFT JOIN altDATA ON creator_id =  altDATA.creator_id

This works:

SELECT * 
FROM `mainDATA` 
LEFT JOIN thirdDATA ON creator_id =  thirdDATA.creator_id
LIMIT 0 , 30

This gets an error:

SELECT * 
FROM `mainDATA` 
LEFT JOIN altDATA ON creator_id =  altDATA.creator_id
JOIN thirdDATA ON creator_id =  thirdDATA.creator_id
Gordon Linoff
2#
Gordon Linoff Reply to 2018-01-12 23:26:02Z

Whenever you have more than one table in a query, qualify all column names.

Presumably, you intend:

SELECT * 
FROM mainDATA LEFT JOIN
     altDATA
     ON mainDATA.creator_id =  altDATA.creator_id JOIN
     thirdDATA
     ON mainDATA.creator_id =  thirdDATA.creator_id;

The behavior you describe would work if mainDATA had no creator_id. If that is the case, the first query would be interpreted as:

SELECT * 
FROM `mainDATA` LEFT JOIN
     altDATA
     ON altDATA.creator_id =  altDATA.creator_id;

The third would generate an error because two tables have creator_id and it is unclear which the last on clause refers to.

Rick James
3#
Rick James Reply to 2018-01-16 00:53:34Z

When mixing JOIN and LEFT JOIN, use parentheses to override the precedence:

SELECT * 
FROM ( `mainDATA` 
       LEFT JOIN altDATA ON creator_id =  altDATA.creator_id )
JOIN thirdDATA ON creator_id =  thirdDATA.creator_id

or

SELECT * 
FROM `mainDATA` 
LEFT JOIN ( altDATA
            JOIN thirdDATA ON creator_id =  thirdDATA.creator_id )
   ON creator_id =  altDATA.creator_id

Doing so will either fix your problem, or point out that creator_id is inaccessible because it is hiding inside parens.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.335888 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO