我想用MYSQL做一个左连接。目前我有一张这样的表：
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)
像这样所需的输出
bookId bookTitle availability
-----------------------------------------------
1 The Da Vinci Code 0
2 Theory of Relativity 0
3 Harry Potter 1
我基本上是在开发一个图书馆管理模块。我希望该书的可用性列在图书搜索页面上。
我目前的代码是：
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
此问题的典型解决方案是什么？提前谢谢。
您需要将where子句中的条件移动到on子句。如果没有匹配的行，则列的值为 NULL，并且WHERE条件失败：
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;
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