php - Left Join - Select ALL from left table but select only the latest from right table

I have 2 tables, borrowers and loans. I want to display on the main page the list of ALL borrowers with or without loans. If with loan, display the newest one.

I have the following sql query, basically it returns the above description except it displays the very first loan of the borrower instead of the latest one.

(Side note: I used GROUP BY to avoid duplicates. Without it the query returns duplicated borrower names if they have multiple loans. Just wanted to know if this is an efficient way of doing so.)

SELECT b.b_id, 
       b.isdeleted, 
       b.picture, 
       b.firstname, 
       b.middlename, 
       b.lastname, 
       b.address, 
       b.contactno,
       b.birthday, 
       b.businessname, 
       b.occupation, 
       b.comaker, 
       b.comakerno, 
       b.remarks, 
       b.datecreated, 
       b.activeloan,
       l.l_id, 
       l.amount, 
       l.payable, 
       l.balance, 
       l.mode, 
       l.term, 
       l.interestrate, 
       l.amortization,
       l.releasedate, 
       l.duedate, 
       l.status, 
       l.c_id
FROM borrowers as b
LEFT JOIN loans as l ON b.b_id = l.b_id
WHERE b.isdeleted = 0
GROUP BY b.b_id
Copy code

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.