There is a request:

SELECT `charges`.*, DATE_FORMAT(`CHARGE_DATE`, '%b %e, %Y') AS `DATE`, `groupclasses`.`CODE` AS `CLASS`, IFNULL((SELECT SUM(`AMOUNT`) FROM `payments_to_charges` WHERE `CHARGE_ID`=`charges`.`ID`),'0.00') AS `AMOUNT_PAID`, CONCAT(IFNULL(FIRSTNAME,''),' ', IFNULL(LASTNAME,'')) AS CLIENT FROM `charges` LEFT JOIN `groupclasses` ON (`charges`.`CLASS_ID`=`groupclasses`.`ID`), `members` WHERE `charges`.`MEMBER_ID`=`members`.`ID` AND `charges`.`ID` > 0 AND `charges`.`MEMBER_ID`= '1031'

I try to expand it:

 SELECT `charges`.*, DATE_FORMAT(`CHARGE_DATE`, '%b %e, %Y') AS `DATE`, `groupclasses`.`CODE` AS `CLASS`, IFNULL((SELECT SUM(`AMOUNT`) FROM `payments_to_charges` WHERE `CHARGE_ID`=`charges`.`ID`),'0.00') AS `AMOUNT_PAID`, CONCAT(IFNULL(FIRSTNAME,''),' ', IFNULL(LASTNAME,'')) AS CLIENT, CONCAT(IFNULL(members2.FIRSTNAME,''),' ', IFNULL(members2.LASTNAME,'')) AS STUDENT FROM `charges` LEFT JOIN `groupclasses` ON (`charges`.`CLASS_ID`=`groupclasses`.`ID`), left join `members` as `members2` on(`charges`.`STUDENT_ID` = members2.ID), `members` WHERE `charges`.`MEMBER_ID`=`members`.`ID` AND `charges`.`ID` > 0 AND `charges`.`MEMBER_ID`= '1031'

As a result, it gives an error. What could be the problem?

  • And what a mistake? - cheops
  • # 1064 - You have an error in your SQL syntax; members2 on the right line, you STUDENT_ID .
  • one
    A comma after the first left join is not needed. Remember all join is written without commas. for the comma is the hidden kind of the word join. - Mike

1 answer 1

In the end, I did everything through join:

 SELECT `charges`.*, DATE_FORMAT(`CHARGE_DATE`, '%b %e, %Y') AS `DATE`, `groupclasses`.`CODE` AS `CLASS`, IFNULL((SELECT SUM(`AMOUNT`) FROM `payments_to_charges` WHERE `CHARGE_ID`=`charges`.`ID`),'0.00') AS `AMOUNT_PAID`, CONCAT(IFNULL(members.FIRSTNAME,''),' ', IFNULL(members.LASTNAME,'')) AS CLIENT, CONCAT(IFNULL(members2.FIRSTNAME,''),' ', IFNULL(members2.LASTNAME,'')) AS STUDENT FROM `charges` inner join `members` on `charges`.`MEMBER_ID`=`members`.`ID` LEFT JOIN `groupclasses` ON (`charges`.`CLASS_ID`=`groupclasses`.`ID`) left join `members` as `members2` on `charges`.`STUDENT_ID` = members2.ID WHERE `charges`.`ID` > 0 AND `charges`.`MEMBER_ID`= '1031'