AnsweredAssumed Answered

ListView of custom module finds no records, log contains MYSQL syntax error, Left Joins do not contain column names for relate fields.

Question asked by Joseph ONeil on Dec 17, 2014
Latest reply on Dec 22, 2014 by EvilPeri EvilPeri
ListView of custom module finds no records, log contains MYSQL syntax error, Left Join statements do not contain the column names for relate fields.

Here is the query from the log:

--------------------------------------------
Wed Dec 17 12:11:43 2014 [25099][1][FATAL]  Query Failed: 

SELECT  crses_trngclasses.id ,
    crses_trngclasses.name ,
    crses_trngclasses.class_number ,
    jt0.name class_type ,
    crses_trngclasses.date_of_class ,
    LTRIM(RTRIM(CONCAT(IFNULL(jt1.first_name,''),' ',IFNULL(jt1.last_name,'')))) host ,
    crses_trngclasses.host_id ,
    LTRIM(RTRIM(CONCAT(IFNULL(jt2.first_name,''),' ',IFNULL(jt2.last_name,'')))) trainer ,
    jt3.name trainer_2 ,
    jt4.name dr2_class_1 ,
    jt5.name dr2_class_2 ,
    crses_trngclasses.assigned_user_id 

FROM crses_trngclasses
 
LEFT JOIN crses_classtypes jt0 ON crses_trngclasses. = jt0.id AND jt0.deleted=0 
LEFT JOIN contacts jt1 ON crses_trngclasses.contact_id1_c = jt1.id AND jt1.deleted=0 
LEFT JOIN contacts jt2 ON crses_trngclasses.contact_id_c = jt2.id AND jt2.deleted=0 
LEFT JOIN accounts jt3 ON crses_trngclasses.account_id_c = jt3.id AND jt3.deleted=0 
LEFT JOIN crses_classtypes jt4 ON crses_trngclasses.crses_classtypes_id1_c = jt4.id AND jt4.deleted=0 
LEFT JOIN crses_classtypes jt5 ON crses_trngclasses. = jt5.id AND jt5.deleted=0
 
where crses_trngclasses.deleted=0

ORDER BY crses_trngclasses.date_entered

DESC LIMIT 0,21:

MySQL error 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

'= jt0.id AND jt0.deleted=0  LEFT JOIN contacts jt1 ON crses_trngclasses.contact_' at line 1
------------------------------------------------

I am on SugarCRM CE Version 6.5.20, running it on a Linux server with Apache2, PHP5 and MYSQL.

The first and last LEFT JOIN statements for jt0 and jt5 both are missing the column names ... which should be: crses_classtypes_id_c  AND crses_classtypes_id2_c respectively. These are relate fields with the Contacts module just as the one for jt4 in this statement.

I would much appreciate any help in understanding how to correct this issue.

Thanks
Joe O

Outcomes