MySQL从另一个表中的计数较低的地方获取

Question

我需要编写一个 mySQL 语句来仅返回尚未租借的项目(书籍)。当我拥有这个但我知道这是不对的时候:

SELECT books.copies 
FROM books 
WHERE books.copies > (SELECT rentals.book_id, COUNT(rentals.book_id) As CountRentals 
                        FROM rentals 
                        WHERE rentals.book_id = books.id )

Answer 1

使用left join 获取rentals 表中没有条目的所有

SELECT b.copies 
FROM books b
LEFT OUTER JOIN rentals r ON r.book_id = b.id
WHERE r.book_id IS NULL