Converting queries from MySQL 4 to MySQL 5
Please support our MySQL advertiser: Programming Forums
Thread Solved
 |
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:
However, on version 5 it does not.
Here is what I had to do to make it work:
As an example, here is another query I had that did not work in VER: 5:
But, it was suggested by dickersonka to try the following, which worked:
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
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
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/...yntax-changes/
its because order of precedence, also always try to do joins this way, makes much cleaner code
http://damonparker.org/blog/2006/02/...yntax-changes/
Custom Application & Software Development
www.houseshark.net
www.houseshark.net
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
Dave
lol easiest way to explain it is be specific
who knows what that means
now this
looks much clearer at least to me
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
Custom Application & Software Development
www.houseshark.net
www.houseshark.net
|
Similar Threads
Other Threads in the MySQL Forum
- Beginners Guide To Creating Database Driven Website (Site Layout and Usability)
- MySQL nested query / joined query conversion help (MySQL)
Other Threads in the MySQL Forum
- Previous Thread: query to find closest
- Next Thread: suggest query
•
•
•
•
Views: 721 | Replies: 3 | Currently Viewing: 1 (0 members and 1 guests)
Linear Mode
