LEFT JOIN with condition is not returning data desired

Question

I would like to do a left join using MYSQL. Currently I have a table something like this:

CREATE TABLE `books` (
  `bookId` int(11) NOT NULL,
  `bookTitle` varchar(100) NOT NULL,
  `bookAuthor` varchar(100) NOT NULL,
  `bookStatus` tinyint(1) NOT NULL,
  `bookDeleteFlag` tinyint(1) NOT NULL
);  
CREATE TABLE `lends` (
  `lendId` int(11) NOT NULL,Primary
  `lendBookId` int(11) NOT NULL,
  `lendBorrowerName` Varchar(100) NOT NULL,
  `lendBorrowStatus` int(11) NOT NULL,
  `lendReturnStatus` int(11) NOT NULL,
  );

insert into books values (1, 'The Da Vinci Code', 'Dan Brown', 1,0)
insert into books values (2, 'Theory of Relativity', 'Albert Einstein', 1,0)
insert into books values (3, 'Harry Potter', 'J K Rowling', 1,0)

insert into books values (1, '1', 'Chris', 1,1)
insert into books values (2, '1', 'Lilly', 1,0)
insert into books values (3, '2', 'Chris', 1,0)
insert into books values (3, '3', 'Chris', 1,1)

The desired output like this

bookId         bookTitle           availability
-----------------------------------------------
  1        The Da Vinci Code            0
  2        Theory of Relativity         0
  3        Harry Potter                 1

I am basically developing a Library management module. I want the availability of that book to be listed on book search page.

The current code that i have is:

SELECT B.bookTitle,
       L.lendBorrowerName AS takenName,
       count(L.lendStatus) AS taken
FROM   books as B
LEFT JOIN lends AS L ON B.bookId = L.lendBookID
WHERE L.lendReturnStatus = 0 // if i remove this code, all rows in books table is listed. However i loose the ability to check the availability of that book
GROUP BY B.bookTitle

What is the typical solution for this problem? Thanks in advance.

Answer 1

You need to move the condition in the where clause to the on clause. When no rows match, then the column has a value of NULL and the WHERE condition fails:

SELECT B.bookTitle,
       L.lendBorrowerName AS takenName,
       count(L.lendStatus) AS taken
FROM   books as B LEFT JOIN
       lends AS L
       ON B.bookId = L.lendBookID AND
          L.lendReturnStatus = 0
GROUP BY B.bookTitle;

Answer 2

SELECT B.bookTitle,
       sum(L.lendReturnStatus = 0) = 0 AS availibility
FROM   books as B
LEFT JOIN lends AS L ON B.bookId = L.lendBookID
GROUP BY B.bookTitle