SQL query to find status of the last issue/return record for each book

Question

Books table

Books issue/return record table

I need a SQL query that finds status of the last issue/return record for each book

This is how far I managed on my own:

SELECT 
    `Ref_No`, 
     IF(`book_issue_chi`.`Status`='Loaned', 'No', 'Yes') as 'Available on loan' 
FROM `books_chi` 
LEFT JOIN `book_issue_chi` ON `book_issue_chi`.`Book_Number`=`books_chi`.`ID` 

This returns correct if the book only have one issue/return record, but it returns wrong if more than one issue/return record on the book. Any help or hint will be welcomed.

books_chi table

book_issue_chi table

mysql query result

How to revise the SQL query that can return status of the last issue/return record for each book? Please help! Thanks.

Answer 1

Find the maximum issue date per book in a subquery:

select *
from books_chi b
left join book_issue_chi bi
  on bi.book_number = b.id
  and (bi.book_number, bi.issue_date) in
  (
    select book_number, max(issue_date)
    from book_issue_chi
    group by book_number
  );

Alternative: As of MySQL 8 you can use a window function (MAX OVER) to get the maximum issue date per book. Thus you read the issue table just once.

select *
from books_chi b
left join
(
  select
    book_issue_chi.*,
    max(issue_date) over(partition by book_number) as max_issue_date
  from book_issue_chi
) bi on bi.book_number = b.id
     and bi.issue_date = bi.max_issue_date;