Query problem moving from mysql 4 to 5 - MySQL

[Justin Koivisto]

I am currently developing a website that is used to display college
course information. I have everything running exactly as intended on my
server which utilizes Mysql 4.1. However, I recently uploaded it to the
college server which has Mysql 5.0 on it.

The query that I am having problems with is as follows:

SELECT
DISTINCT course.id,
course.courseid,
course.subject,
course.coursenumber,
course.section,
course.title,
course.credits,
course.type,
FORMAT(course.taken, 0),
FORMAT(course.total, 0),
course.extrainfo,
course.gradelevel,
course.semester,
DATE_FORMAT(FROM_UNIXTIME(courseinfo.starttime), "%h:%i %p"),
DATE_FORMAT(FROM_UNIXTIME(courseinfo.endtime), "%h:%i %p"),
courseinfo.days,
courseinfo.faculty,
courseinfo.room,
dept.title,
DATE_FORMAT(FROM_UNIXTIME(courseinfo.starttime), "%m/%e/%Y"),
DATE_FORMAT(FROM_UNIXTIME(courseinfo.endtime), "%m/%e/%Y"),
DATE_FORMAT(FROM_UNIXTIME(semester.startdate), "%m/%e/%Y"),
DATE_FORMAT(FROM_UNIXTIME(semester.enddate), "%m/%e/%Y")
FROM
course
INNER JOIN courseinfo
INNER JOIN dept
INNER JOIN semester
ON semester.id = course.semester
ON dept.subject = course.subject
ON courseinfo.courseid = course.courseid
AND courseinfo.semester = course.semester
WHERE
course.semester = 2
AND (course.subject = 'CSCI')
ORDER BY
dept.title ASC,
course.subject ASC,
course.coursenumber ASC,
course.section ASC

The error that is currently being reported is:

You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'ON
dept.subject = course.subject ON courseinfo.courseid = course.courseid
A' at line 1

Could someone point me in the right direction or give me some insight as
to what is happening here? TIA!

--
Justin Koivisto, ZCE - [email]justinkoivi.com[/email]
[url]http://koivi.com[/url]

[Bill Karwin]

"Justin Koivisto" <justinkoivi.com> wrote in message
news:xbqdndSdwOnTdWTe4p2dnAonvoy.com...

> FROM
> course
> INNER JOIN courseinfo
> INNER JOIN dept
> INNER JOIN semester
> ON semester.id = course.semester
> ON dept.subject = course.subject
> ON courseinfo.courseid = course.courseid
> AND courseinfo.semester = course.semester
>
> The error that is currently being reported is:
>
> You have an error in your SQL syntax; check the manual that corresponds
> to your MySQL server version for the right syntax to use near 'ON
> dept.subject = course.subject ON courseinfo.courseid = course.courseid
> A' at line 1

I don't usually see join syntax like yours; I'm not sure why it worked on
MySQL 4.1.

I'd use this form:
FROM course
INNER JOIN courseinfo ON courseinfo.courseid = course.courseid
INNER JOIN dept ON dept.subject = course.subject
INNER JOIN semester ON semester.id = course.semester AND
courseinfo.semester = course.semester

You may also need to use parentheses to force appropriate order of
evaluation of the joins. There has been a change in MySQL 5.0 that affects
precedence of JOIN operations, to bring MySQL closer to the ANSI SQL
standard.

See the discussion in this thread:
[url]http://groups.google.com/group/mailing.database.mysql/browse_thread/thread/eee40e1bd0a0464b/[/url]

Regards,
Bill K.

[Justin Koivisto]

Bill Karwin wrote:

> "Justin Koivisto" <justinkoivi.com> wrote in message
> news:xbqdndSdwOnTdWTe4p2dnAonvoy.com...

>> FROM
>> course
>> INNER JOIN courseinfo
>> INNER JOIN dept
>> INNER JOIN semester
>> ON semester.id = course.semester
>> ON dept.subject = course.subject
>> ON courseinfo.courseid = course.courseid
>> AND courseinfo.semester = course.semester
>>
>> The error that is currently being reported is:
>>
>> You have an error in your SQL syntax; check the manual that corresponds
>> to your MySQL server version for the right syntax to use near 'ON
>> dept.subject = course.subject ON courseinfo.courseid = course.courseid
>> A' at line 1

>
> I don't usually see join syntax like yours; I'm not sure why it worked on
> MySQL 4.1.
>
> I'd use this form:
> FROM course
> INNER JOIN courseinfo ON courseinfo.courseid = course.courseid
> INNER JOIN dept ON dept.subject = course.subject
> INNER JOIN semester ON semester.id = course.semester AND
> courseinfo.semester = course.semester
>
> You may also need to use parentheses to force appropriate order of
> evaluation of the joins. There has been a change in MySQL 5.0 that affects
> precedence of JOIN operations, to bring MySQL closer to the ANSI SQL
> standard.
>
> See the discussion in this thread:
> [url]http://groups.google.com/group/mailing.database.mysql/browse_thread/thread/eee40e1bd0a0464b/[/url]

Thanks! This appears to be working exactly as the old query was:

INNER JOIN courseinfo ON courseinfo.courseid = course.courseid AND
courseinfo.semester = course.semester

INNER JOIN dept ON dept.subject = course.subject
INNER JOIN semester ON semester.id = course.semester

--
Justin Koivisto, ZCE - [email]justinkoivi.com[/email]
[url]http://koivi.com[/url]