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:

whats the result that shows after execute your model query ? — Naveen K, Mar 10 '19 at 05:25
hello Naveen! The result always show for the first row, it mean it show for row accountcode 4 — Lorn Titya, Mar 10 '19 at 07:40
Yes, tbl_customer has many tbl_cus_account. It mean one to many — Lorn Titya, Mar 10 '19 at 08:39

Nerea · Accepted Answer · 2019-03-10T15:55:48.487

0

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');

edited Mar 10 '19 at 15:55

answered Mar 10 '19 at 08:56

Nerea

2,107
2
15
14

it has error with join table of customer with tbl_cus_account ->join('tbl_customer', 'tbl_customer.cus_code = tbl_cus_account.custcode') and sum monthlyfee is not right of amount – Lorn Titya Mar 10 '19 at 09:39
I updated my anser, now the join with tbl_customer is inside the new join. The error with the join tbl_customer has to be because of a error on a name with the columns relation if you let me know the correct names I will change it. – Nerea Mar 10 '19 at 10:08
Nerea, it still error as the same of join table. And the key relation of these two are tbl_customer(cus_code) and tbl_cus_account(custcode) – Lorn Titya Mar 10 '19 at 14:57
Hi Nerea! Thank you so much for your help. – Lorn Titya Mar 10 '19 at 15:19
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'); – Lorn Titya Mar 10 '19 at 15:21
pls update your answer and i will click right for you. – Lorn Titya Mar 10 '19 at 15:22

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

1 Answers1