MySQL 5 multiple JOIN syntax not working in MySQL 4
- by draco
Hello all, the current SQL query works fine locally on MAMP 1.8.4 running MySQL 5.1.37.
SELECT
EL.log_actions, EL.log_date, EL.log_value, EL.log_type,
EA.admins_name,
EU.users_name, EU.users_matric
FROM events_log EL
JOIN events_users EU
USING (users_id)
JOIN events_admins EA
USING (admins_id)
ORDER BY EL.log_id DESC
LIMIT 0, 10
However, when I bring this query live to production server which is running MySQL 4.1.22-standard, the following error occurred (whether or not there are data in the entry).
A Database Error Occurred
Error Number: 1054
Unknown column 'sceclub_exclaim2007.EU.admins_id' in 'on clause'
SELECT EL.log_actions, EL.log_date, EL.log_value, EL.log_type,
EA.admins_name, EU.users_name, EU.users_matric
FROM events_log EL
JOIN events_users EU USING (users_id)
JOIN events_admins EA USING (admins_id)
ORDER BY EL.log_id
DESC LIMIT 0, 20
This is based on CodeIgniter 1.7.2 and both production and development are running the same set of database.
Database tables
events_users: users_id users_name users_credits
users_matric users_redeem
events_admins: admins_id admins_email admins_name
admins_pass admins_date admins_modified admins_last_login
events_attendance: attendance_id users_id events_id
events_events: events_id events_name events_venue events_time
events_desc events_pass
events_log:log_id admins_id log_actions log_date log_value
users_id log_type
I'm new to MySQL so I'm not aware of any difference in versions or what could be a possible cause, thank you in advance!
Tried googling for MySQL4 difference to no avail too. Also tried using
SELECT
EL.log_actions, EL.log_date, EL.log_value, EL.log_type,
EA.admins_name,
EU.users_name, EU.users_matric
FROM events_log EL
JOIN events_users EU
where EL.users_id = EU.users_id
JOIN events_admins EA
USING EL.admins_id = EA.admins_id
ORDER BY EL.log_id DESC
LIMIT 0, 10
But then I got the error in both production and development.
A Database Error Occurred
Error Number: 1064
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 'JOIN
events_admins EA USING EL.admins_id =
EA.admins_id ORDER BY' at line 8
SELECT EL.log_actions, EL.log_date,
EL.log_value, EL.log_type,
EA.admins_name, EU.users_name,
EU.users_matric FROM events_log EL
JOIN events_users EU where EL.users_id
= EU.users_id JOIN events_admins EA USING EL.admins_id = EA.admins_id
ORDER BY EL.log_id DESC LIMIT 0, 20
If you can point me to some resources where I can read up more on MySQL 4 syntax to achieve the same thing effect like I did with MySQL 5 syntax, please let me know.
Thanks again!
