SEO

#1 (**permalink**) 02-27-2008, 09:30 PM

I am going to upgrade my database from version 4 to 5.
However I found some of my web application doesn't work on MySQL5

e.g following statement works in 4 but not 5

SELECT f.*, c.id as cat_id, c.position as cat_position, c.state as
cat_state, c.name as cat_name, c.description as cat_desc,
c.image, c.url, m.member_name as mod_name, m.member_id as
mod_id, m.is_group, m.group_id, m.group_name, m.mid
FROM ibf_forums f, ibf_categories c
LEFT JOIN ibf_moderators m ON (f.id=m.forum_id)
WHERE c.id=f.category
ORDER BY c.position, f.position

Error:
Unknown column 'f.id' in 'on clause'

The alias seem not working?
What should I do, I dont want to rewrite all my sql statement....

Thanks.

Jason

#2 (**permalink**) 02-27-2008, 09:30 PM

Jason

following statement works in 4 but not 5

SELECT f.*, c.id as cat_id, c.position as cat_position, c.state as
cat_state, c.name as cat_name, c.description as cat_desc,
c.image, c.url, m.member_name as mod_name, m.member_id as
mod_id, m.is_group, m.group_id, m.group_name, m.mid
FROM ibf_forums f, ibf_categories c
LEFT JOIN ibf_moderators m ON (f.id=m.forum_id)
WHERE c.id=f.category
ORDER BY c.position, f.position

Look up joins in the 5.0 or 5.1 manual. As of 5.0.12, MySQL implemented
ANSI/ISO compliance, breaking comma joins of the sort you use above. You
need explicit JOIN ... ON | USING syntax to remove referential ambiguities.

PB

-----

Jason Chan wrote:
> I am going to upgrade my database from version 4 to 5.
> However I found some of my web application doesn't work on MySQL5
>
> e.g following statement works in 4 but not 5
>
> SELECT f.*, c.id as cat_id, c.position as cat_position, c.state as
> cat_state, c.name as cat_name, c.description as cat_desc,
> c.image, c.url, m.member_name as mod_name, m.member_id as
> mod_id, m.is_group, m.group_id, m.group_name, m.mid
> FROM ibf_forums f, ibf_categories c
> LEFT JOIN ibf_moderators m ON (f.id=m.forum_id)
> WHERE c.id=f.category
> ORDER BY c.position, f.position
>
> Error:
> Unknown column 'f.id' in 'on clause'
>
> The alias seem not working?
> What should I do, I dont want to rewrite all my sql statement....
>
> Thanks.
>
> Jason
>
>
>
>
>
>

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.408 / Virus Database: 268.13.1/470 - Release Date: 10/10/2006

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

SQL statement work in mysql4 but not mysql5