Mysql中的mysql错误

Question

SELECT s.enrollment_id , s.first_name ,s.last_name,
     s.nic, d.description_short, s.fee_pkg, sch.school_id,
      p.program_id, se.session_id, s.date_of_birth, s.gender,
      dis.description discount, s.final_pkg, s.installment_amount,
       ins.installment_plan, s.father_name, s.mobile, s.landline,
       s.email, cump.campus_name, city.city_name, s.country_id,
       s.province
     FROM ums_student s , ums_departments d ,
      ums_schools sch, ums_programs p,
       ums_sessions se, ums_discount_type dis,
        ums_inst_plan ins, ums_campus cump ,
        ums_city city
        LEFT JOIN ums_departments ON  d.id=s.department_id and 
        LEFT JOIN ums_schools ON  sch.school_id=s.school_id and
        LEFT JOIN ums_programs ON p.program_id=s.program_id and
        LEFT JOIN ums_sessions ON se.session_id=s.session_id and
        LEFT JOIN ums_discount_type ON dis.discount_id=s.discount and
        LEFT JOIN ums_inst_plan ON ins.id=s.installment_plan and
        LEFT JOIN ums_campus ON cump.id=s.campus_id and
        LEFT JOIN ums_city ON city.id=s.city_id;

我正在尝试这个查询并得到这个错误。

Error Code : 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 ums_programs ON p.program_id=s.program_id and
            LEFT JOIN ums_sessions O' at line 14

请帮帮我。

Answer 1

您不需要--也不可能--在每个and之前使用LEFT JOIN。另外，您不需要在执行ands之前列出表，这样做实际上是对每个表进行了两次JOIN处理，一次是作为CROSS JOIN，这是非常低效率的。

您的SQL应该如下所示：

SELECT s.enrollment_id , s.first_name ,s.last_name,
     s.nic, d.description_short, s.fee_pkg, sch.school_id,
     p.program_id, se.session_id, s.date_of_birth, s.gender,
     dis.description discount, s.final_pkg, s.installment_amount,
      ins.installment_plan, s.father_name, s.mobile, s.landline,
      s.email, cump.campus_name, city.city_name, s.country_id,
       s.province
 FROM ums_student s
     LEFT JOIN ums_departments d ON  d.id=s.department_id
     LEFT JOIN ums_schools sch ON  sch.school_id=s.school_id
     LEFT JOIN ums_programs p ON p.program_id=s.program_id
     LEFT JOIN ums_sessions se ON se.session_id=s.session_id
     LEFT JOIN ums_discount_type dis ON dis.discount_id=s.discount
     LEFT JOIN ums_inst_plan ins ON ins.id=s.installment_plan
     LEFT JOIN ums_campus cump ON cump.id=s.campus_id
     LEFT JOIN ums_city city ON city.id=s.city_id;

Answer 2

在所有联接子句之后，您必须删除结尾。它不是在那里被分配的。

SELECT s.enrollment_id , s.first_name ,s.last_name,
     s.nic, d.description_short, s.fee_pkg, sch.school_id,
      p.program_id, se.session_id, s.date_of_birth, s.gender,
      dis.description discount, s.final_pkg, s.installment_amount,
       ins.installment_plan, s.father_name, s.mobile, s.landline,
       s.email, cump.campus_name, city.city_name, s.country_id,
       s.province
     FROM ums_student s , ums_departments d ,
      ums_schools sch, ums_programs p,
       ums_sessions se, ums_discount_type dis,
        ums_inst_plan ins, ums_campus cump ,
        ums_city city
        LEFT JOIN ums_departments ON  d.id=s.department_id 
        LEFT JOIN ums_schools ON  sch.school_id=s.school_id 
        LEFT JOIN ums_programs ON p.program_id=s.program_id 
        LEFT JOIN ums_sessions ON se.session_id=s.session_id 
        LEFT JOIN ums_discount_type ON dis.discount_id=s.discount 
        LEFT JOIN ums_inst_plan ON ins.id=s.installment_plan 
        LEFT JOIN ums_campus ON cump.id=s.campus_id 
        LEFT JOIN ums_city ON city.id=s.city_id;