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.
You need to move the condition in the
where
clause to theon
clause. When no rows match, then the column has a value ofNULL
and theWHERE
condition fails: