How to join tables that depends on each other

Let's say I am making a book lending database.

drop table lend_current;
drop table lend_history;
drop table books;

create table books ( id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT);

create table lend_history (
 id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT, 
 book_id int(11) NOT NULL,
 lender_name VARCHAR(50) NOT NULL,
 CONSTRAINT FK_books FOREIGN KEY(book_id) REFERENCES books(id));

create table lend_current (
  lend_history_id int(11) PRIMARY KEY NOT NULL,
  CONSTRAINT FK_lendhistory FOREIGN KEY(lend_history_id) REFERENCES lend_history(id));

INSERT INTO books (id) VALUE(1);
INSERT INTO books (id) VALUE(2);

INSERT INTO lend_history (id, book_id, lender_name) VALUES(1, 1, "mike");
INSERT INTO lend_history (id, book_id, lender_name) VALUES(2, 2, "jane");
INSERT INTO lend_history (id, book_id, lender_name) VALUES(3, 2, "ola");  /* this will be current lender */

INSERT INTO lend_current (lend_history_id) VALUES(3);

SELECT books.id, lend_history.lender_name FROM books
LEFT JOIN lend_history on lend_history.book_id=books.id
LEFT JOIN lend_current on lend_current.lend_history_id=lend_history.id

I want my query to lists all the books with the current lender's name but if nobody lent the book, it should still list that book with name field NULL.

this query lists the book with id (2) twice. But I want it to show only once with the current lender's name.

I tried with inner join, right join but couldn't achieve it. What am I missing?

I used to have "is_current" column in the lend_history before but after reading Could I make a column in a table only allows one 'true' value and all other rows should be 'false' I decided to create a separate table for better practices.

Solution

You cannot have straight joins between the three tables, because of your handling of "is_current" column. One way would be to use Derived Tables (subquery); in the subquery, you can get all lend history items, which are "current". Now simply LEFT JOIN the subquery result to the books table:

SELECT b.id, dt.lender_name 
FROM books AS b 
LEFT JOIN 
( 
  SELECT lh.book_id, lh.lender_name
  FROM lend_current AS lc 
  JOIN lend_history AS lh ON lh.id = lc.lend_history_id
) AS dt ON dt.book_id = b.id

View on DB Fiddle

Result

| id  | lender_name |
| --- | ----------- |
| 1   |             |
| 2   | ola         |

Sidenote: Instead of creating a third table, lend_current, I would have preferred to stick to your original schema of having two tables, books, and lend_history. Now to determine which history row is current, we can have two ways:

Your original approach: Either have is_current field in the lend_history table (which will be 1 if current row.
OR, We can have a current_history_id field in the books table storing the id of current history row. It will be NULL if the book is not being lent currently. This can also utilize Foreign Key constraint as well.