Query to display latest values based from the latest dates

Question

I'm creating a school project where I need to monitor the member's availability based on their loan transactions.

Now the issue here is when a member already makes tons of transactions, my existing query displays all of the transactions which I only wanted the present status based on their latest date requested of a transaction.

I will provide a screenshot of the issue for more visualization.

This is my query as of now:

$query = $this->db->query("
  SELECT * FROM `tbl_members` 
  LEFT JOIN tbl_loanrequests 
  on tbl_members.member_id = tbl_loanrequests.member_id
");

And here's the useful information:

CREATE TABLE `tbl_members` (
  `member_id` int(11) NOT NULL,
  `firstname` varchar(250) NOT NULL,
  `lastname` varchar(250) NOT NULL,
  `middlename` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `tbl_members` (`member_id`, `firstname`, `lastname`, `middlename`) VALUES
(1,  'Maribeth', 'Cunha', ''),
(2,  'Thelma ', 'Rush  ', ''),
(3,  'Latoria ', 'Shinn', ''),
(4,  'Quinton ', 'Everman', ''),
(5, 'Robert', 'Evermen', '');

CREATE TABLE `tbl_loanrequests` (
  `loanreq_id` int(11) NOT NULL,
  `member_id` int(11) NOT NULL,
  `loanreq_status` varchar(50) NOT NULL,
  `date_requested` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `tbl_loanrequests` (`loanreq_id`, `member_id`, `loanreq_status`, `date_requested`) VALUES
(2, 1, 'Paid', '2021-05-06'),
(3, 2, 'For Release', '2021-05-06'),
(4, 3, 'Pending', '2021-05-06'),
(5, 5, 'Ongoing', '2021-05-06'),
(6, 1, 'Cancelled', '2021-05-07');

http://sqlfiddle.com/#!9/7647a4/1

This is the preview where member_id #1 supposed to be displaying only one status which is the Cancelled since it got the latest date_requested. On the left side, it displays Clear though but that's a way for me to monitor if a member is able to file a loan.

can you use your existing DDL scripts to setup sql-fiddle, would be easy to get help. — Harsh Gundecha, May 07 '21 at 05:18
@HarshGundecha Thank you. I just discovered sql-fiddle just now and I will use it for future questions. Also, I updated my post and put an sql-fiddle for others' reference. :) — akamariaclaraaa, May 07 '21 at 05:43

score 1 · Accepted Answer · answered May 07 '21 at 05:32

I considered that your loanreq_id is incremented and thus that the highest for each member always contain the highest loanreq date for that member. If not I can adapt

The trick is to determine the highest loanreq_id for each member using a MAX...GROUP BY:

SELECT member_id, MAX(loanreq_id) AS last_loanreq_id
FROM tbl_loanrequests
GROUP BY member_id

You should then join the result of this query to only keep the latest loanreq of each member. Your query becomes :

SELECT m.*, l.*
FROM `tbl_members` m
LEFT JOIN
    (
    SELECT member_id, MAX(loanreq_id) AS last_loanreq_id
    FROM tbl_loanrequests
    GROUP BY member_id
    ) lm ON lm.member_id = m.member_id 
LEFT JOIN tbl_loanrequests l on l.loanreq_id = lm.last_loanreq_id

Query to display latest values based from the latest dates

1 Answers1