How to retrieve record in group by with join table depend on MAX?

Question

This my query in model:

return $this->db->join('tbl_customer', 'tbl_customer.cus_code = tbl_cus_account.custcode')
                ->where("status", 1)
                ->where("DATE_FORMAT(nextbillingdate,'%Y-%m') <= ", date('Y-m'))
                ->select('*,MAX(issuewithmain) AS issuewithmain, SUM(monthlyfee) AS monthlyfee, count(accountcode) as rows')
                ->group_by('custcode')
                ->get('tbl_cus_account');

• The below are my table for join query:

-The result I want as the image below:

Answer 1

Your query can be something like this

SELECT t.*, t2.rows, t2.monthlyfee from tbl_cus_account t join (SELECT MAX(issuewithmain) AS issuewithmain, custcode, count(accountcode) as rows, SUM(monthlyfee) AS monthlyfee from tbl_cus_account JOIN tbl_customer ON tbl_customer.cus_code = tbl_cus_account.custcode  where status = 1 AND DATE_FORMAT(nextbillingdate,"%Y-%m") <= DATE_FORMAT(now(),"%Y-%m") GROUP BY custcode) t2 on t.issuewithmain = t2.issuewithmain and t.custcode = t2.custcode

Here you made an extra join to the same table, to only get the exactly records that match with your max(issuewithmain)

You need to transform this query to codeigniter and add the conditions of the where to the corresponding table.

Maybe something like this, I haven't test it

UPDATE

return $this->db->join('(SELECT MAX(issuewithmain) AS issuewithmain, custcode, count(accountcode) as rows, SUM(monthlyfee) AS monthlyfee from tbl_cus_account where status = 1 AND DATE_FORMAT(nextbillingdate,"%Y-%m") <= "'.date('Y-m').'" GROUP BY custcode) t2', 'tbl_cus_account.issuewithmain = t2.issuewithmain AND t2.custcode = tbl_cus_account.custcode')->select('tbl_cus_account.*, t2.monthlyfee, t2.rows') ->join('tbl_customer', 'tbl_customer.cus_code = t2.custcode') ->group_by('t2.custcode') ->get('tbl_cus_account');