That's because you are still using comma joins! > ... FROM mos_content c, <---- one comma > mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id = ^--- another comma > c.access) INNER JOIN mos_users u ON (u.id = c.checked_out) INNER JOIN > mos_users v ON (v.id = c.created_by) INNER JOIN mos_content_frontpage 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 c.sectionid='1' ...
If you feel uncomfortable making an INNER JOIN with no restrictions (no "ON" clause) you can call it a CROSS JOIN instead. Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Ian Barnes" <[EMAIL PROTECTED]> wrote on 01/24/2006 03:15:24 PM: > Hi, > > It now looks like this and still doesn't work, complains about exactly the > same thing. > > 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 c, > mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id = > c.access) INNER JOIN mos_users u ON (u.id = c.checked_out) INNER JOIN > mos_users v ON (v.id = c.created_by) INNER JOIN mos_content_frontpage 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 c.sectionid='1' ORDER BY > cc.ordering, cc.title, c.ordering LIMIT 0,10; > > Thanks, > Ian > > -----Original Message----- > From: gerald_clark [mailto:[EMAIL PROTECTED] > Sent: 24 January 2006 09:50 PM > To: Ian Barnes > Cc: mysql@lists.mysql.com > Subject: Re: Query Help > > Ian Barnes wrote: > > >Hi, > > > >This is my current query which works in mysql 4, but not in 5. Its from > >mambo, but im trying to modify it because they don't officially support > >mysql5 yet. > > > >The original query: > >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 c.sectionid='1' ORDER BY > >cc.ordering, cc.title, c.ordering LIMIT 0,10 > > > >My modified version: > >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 c, > >mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id = > >c.access) LEFT JOIN mos_users u ON (u.id = c.checked_out) LEFT JOIN > >mos_users v ON (v.id = c.created_by) LEFT JOIN mos_content_frontpage 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 c.sectionid='1' ORDER BY > >cc.ordering, cc.title, c.ordering LIMIT 0,10; > > > >The error I get for both ones is: Unknown column 'c.access' in 'on clause' > > > >Thanks and sorry for the stupid question. > > > >Cheers > >Ian > > > > > > > > > > > Replace all your comma joins to INNER JOIN syntax > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >