I have case where I need latest status. There are two tables, I simplify my table so it looks like below:
Table A
+------------+
| a_id |
|------------|
| A1 |
| A2 |
+------------+
Table B
+------------+-------------+------------------+------------------+
| b_id | a_id | status | created_at |
|------------+-------------+------------------+------------------|
| B01 | A1 | something | 2020-03-14 |
| B02 | A1 | something else | 2020-04-15 |
| B03 | A2 | anything | 2020-03-22 |
+------------+-------------+------------------+------------------+
I want to show table from A with join table B so it will show like this:
+------------+--------------------+-----------------+
| a.a_id | b.status | b.created_at |
|------------+--------------------+-----------------|
| A1 | something else | 2020-04-15 |
| A2 | anything | 2020-03-22 |
+------------+--------------------+-----------------+
I will appreciate Mysql query or codeigniter query builder. Thank you
If you're running MySQL 8.0, you can do this with row_number() :
select a.a_id, b.status, b.created_at from tablea a inner join ( select b.*, row_number() over(partition by a_id order by created_at desc) rn from tableb b ) b on a.a_id = b.a_id and b.rn = 1In older versions, one option is to filter with a correlated subquery:
select a.a_id, b.status, b.created_at from tablea a inner join tableb b on a.a_id = b.a_id where b.created_at = ( select max(b1.created_at) from tableb b1 where b1.a_id = b.a_id ) For performance with the correlated subquery solution, consider an index on tableb(a_id, created_at) .
First find the last created_at value from table B
Example: SELECT MAX(created_at) FROM B GROUP BY a_id;
Then use the first result in the query below and you will get the desired result.
SELECT FROM A JOIN B ON A.a_id = B.a_id WHERE B.created_at IN (SELECT MAX(created_at) FROM B GROUP BY a_id);