0

I am using Codeigniter, I have an issue with MySQL query. I tried from my side but it's not getting my expected output.

After getting the answer from @tcadidot0, my issue got resolved but I have to use the below query with my joins.

SELECT A.* FROM tbl_payment_invoice A 
INNER JOIN
(SELECT `bank_id`,MAX(`payment_invoice_date`) AS maxdt 
   FROM tbl_payment_invoice GROUP BY `bank_id`) B
ON A.`bank_id`=B.`bank_id` AND A.`payment_invoice_date`=B.`maxdt`;

Can you help me out how can I use it above query in this join ->join('tbl_payment_invoice','tbl_bankname.b_id=tbl_payment_invoice.bank_id','LEFT')?

Final query

$result = $this->db->select('*')
                    ->from('tbl_lead')
                    ->join('tbl_bankdata','tbl_lead.c_id=tbl_bankdata.lead_id','LEFT')
                    ->join('tbl_bankname','tbl_bankname.b_id=tbl_bankdata.b_bankname')
                    ->join('tbl_payment_invoice','tbl_bankname.b_id=tbl_payment_invoice.bank_id','LEFT')
                    ->order_by('tbl_lead.date_of_created','DESC')
                    ->get()
                    ->result();

Should I need to use a subquery?

Naren Verma
  • 2,205
  • 5
  • 38
  • 95
  • 1) When you use aggregate functions, you have to GROUP BY remaining columns in SELECT. Try this query: select bank_id, max(payment_invoice), max(payment_invoice_date) as dateofadded from tbl_payment_invoice group by bank_id; Is it what you wanted to achieve? – equi Oct 08 '19 at 07:18
  • @equi, I thought I can't use max(payment_invoice). can you help me with the second issue? How to use the same query in CodeIgniter? – – Naren Verma Oct 08 '19 at 08:22
  • @Strawberry, I updated the question and descriptions. – Naren Verma Oct 13 '19 at 17:46

1 Answers1

1

You can try with this:

SELECT A.* FROM tbl_payment_invoice A 
INNER JOIN
(SELECT `bank_id`,MAX(`payment_invoice_date`) AS maxdt 
   FROM tbl_payment_invoice GROUP BY `bank_id`) B
ON A.`bank_id`=B.`bank_id` AND A.`payment_invoice_date`=B.`maxdt`;

First you get the latest date using MAX() group by bank_id. Then make that as a sub-query. Create an outer query to inner join with the result you got on bank_id and max date.

FanoFN
  • 6,815
  • 2
  • 13
  • 33