Unknown column 'c.access' in 'on clause'

[cfsponge]

I'm puzzled as to why this query is not working. There is a column named 'access' in the mos_content table (int(11) unsigned). I get the mysql error above when executing.


SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author
FROM mos_content AS c, mos_categories AS cc, mos_sections AS s
LEFT JOIN mos_groups AS g ON g.id = c.access
LEFT JOIN mos_users AS u ON u.id = c.checked_out
LEFT JOIN mos_users AS v ON v.id = c.created_by
LEFT JOIN mos_content_frontpage AS f ON f.content_id = c.id WHERE c.state >= 0 AND c.catid=cc.id AND cc.section=s.id AND s.scope='content' AND YEAR(c.created) = '2006'
ORDER BY s.title, c.catid, cc.ordering, cc.title, c.ordering LIMIT 0,30

 

[r937]

seen this before

this worked in previous versions, no longer works in 5.0 which is more strict about adhering to ansi syntax

FROM ... mos_sections AS s
LEFT JOIN mos_groups AS g ON g.id = c.access

note here that the two tables are being joined on a column that is in neither table!

hence the error

the reason is you are mixing "comma list" syntax with JOIN syntax

by default, this is being parsed like this --

FROM mos_content AS c, mos_categories AS cc, [COLOR=red][b]([/b][/color] mos_sections AS s
LEFT JOIN mos_groups AS g ON g.id = c.access
LEFT JOIN mos_users AS u ON u.id = c.checked_out
LEFT JOIN mos_users AS v ON v.id = c.created_by
LEFT JOIN mos_content_frontpage AS f ON f.content_id = c.id [COLOR=red][b])[/b][/color]

to fix your problem, use JOIN syntax throughout --

  from mos_content as c
inner
  join mos_categories as cc
    on cc.id = c.catid
inner
  join mos_sections as s
    on s.id = cc.section
left outer
  join mos_groups as g 
    on g.id = c.access
left outer
  join mos_users as u 
    on u.id = c.checked_out
left outer
  join mos_users as v 
    on v.id = c.created_by
left outer
  join mos_content_frontpage as f 
    on f.content_id = c.id

r937.com | rudy.ca

 

[cfsponge]

Thank you. I would have never realized the logic issue with the syntax.