MySQL 5 multiple JOIN syntax not working in MySQL 4

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!

Answers


 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 AS EL
  JOIN events_users AS EU
    ON EL.users_id = EU.users_id
  JOIN events_admins EA
    ON EL.admins_id = EA.admins_id
  ORDER BY EL.log_id DESC
  LIMIT 0, 10

The second query you tried should have the keyword ON, not USING or where:

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
  ON EL.users_id = EU.users_id
JOIN events_admins EA
  ON EL.admins_id = EA.admins_id
ORDER BY EL.log_id DESC
LIMIT 0, 10

Have a look at the syntax for JOIN if you are still unsure whether to use WHERE, ON or USING.


Need Your Help

C++ How to reassign a pointer to an object with this as argument?

c++ object this nullptr

I need to make a pointer to an object of a class. However, it is declared as nullptr first. I need to make it point to that class.