Converting queries from MySQL 4 to MySQL 5

[filch]

OK .. I was migrating an application from a staging server where the MySQL version is 4.1.10a to a live server where the MySQL version is 5.0.45. There apparently is quite a difference between query syntax on one versus the other.

For example, in version 4, this worked:

SELECT uas.usr_id_users, uas.airport_id_airport, a.airport_code, a.airport_name, a.airport_id, MAX(if(service_id_service=1, 1, 0)) AS JET, MAX(if(service_id_service=2, 1, 0)) AS GROUND, MAX(if(service_id_service=3, 1, 0)) AS GLYCOL, u.usr_fname AS FIRSTNAME, u.usr_lname AS LASTNAME, u.usr_email AS EMAIL
FROM userairportservices uas, users u LEFT JOIN airport a ON a.airport_id = uas.airport_id_airport
WHERE uas.usr_id_users = usr_id
GROUP BY airport_id_airport LIMIT 0, 30

However, on version 5 it does not.

Here is what I had to do to make it work:

SELECT uas.usr_id_users, uas.airport_id_airport, a.airport_code, a.airport_name, a.airport_id, MAX( if( service_id_service =1, 1, 0 ) ) AS JET, MAX( if( service_id_service =2, 1, 0 ) ) AS GROUND, MAX( if( service_id_service =3, 1, 0 ) ) AS GLYCOL, u.usr_fname AS FIRSTNAME, u.usr_lname AS LASTNAME, u.usr_email AS EMAIL
FROM users u LEFT JOIN userairportservices uas ON uas.usr_id_users =usr_id LEFT JOIN airport a ON a.airport_id = uas.airport_id_airport
GROUP BY airport_id_airport LIMIT 0 , 30

As an example, here is another query I had that did not work in VER: 5:

SELECT usr_id, usr_access, usr_title, usr_fname, usr_lname, usr_email, access.access, title.title
FROM users INNER JOIN access, title ON access_id = usr_access AND title_id = usr_title

But, it was suggested by dickersonka to try the following, which worked:

SELECT u.usr_id, u.usr_access, u.usr_title, u.usr_fname, u.usr_lname, u.usr_email, a.access, t.title
FROM users u INNER JOIN access a ON a.access_id = u.usr_access INNER JOIN title t ON t.title_id = u.usr_title

So I post this here not to ask for help, which I have received plenty of here, but in the hopes this helps someone else who runs into this issue.

Cheers,

Dave

[dickersonka]

here i found an article on the suggestion i gave you earlier

its because order of precedence, also always try to do joins this way, makes much cleaner code

http://damonparker.org/blog/2006/02/23/mysql5-join-syntax-changes/

[filch]

Cheers ... I had a quick glance at this and the scarey thing is, for the most part, I understood it. Not perfectly clear but certainly not overly cloudy either.

Dave

[dickersonka]

lol easiest way to explain it is be specific

from table1, table2 join table3 on id = table3.id

who knows what that means

now this

from table1 inner join table2 on table1.id = table2.id 
inner join table 3 on table3.id = table2.id

looks much clearer at least to me