我在SQLite数据库中有三个表：book，author和loaned_books。他们看起来像这样
书： _id | author_id |标题|主题
作者： _id | first_name | last_name
贷款书： _id | book_id |贷款日期|截止日期
author_id是作者表的外键，book_id是book表的外键。
我正在尝试创建一个查询，以提取所有未借出的书（及其关联的作者）。到目前为止，我有这个查询：
SELECT book.title,book.subject,author.first_name,author.last_name
FROM book,author
INNER JOIN loaned_books ON book._id != loaned_books.book_id AND book.author_id = author._id
除了重复执行的所有记录都是重复的以外，这可以完成这项工作。我曾尝试在列名上使用DISTINCT，但结果也是错误的。
将返回所有未借出的书（及其相关作者）的查询是什么？
您加入book和author的方式：
FROM book,author
错误，因为它返回2个表的笛卡尔积。
您需要向loaned_books进行LEFT JOIN并返回不匹配的行（这意味着loaned_books中不存在的书）
SELECT b.title,b.subject,a.first_name,a.last_name
FROM book AS b
INNER JOIN author AS a ON b.author_id = a._id
LEFT JOIN loaned_books AS l ON b._id = l.book_id
WHERE l._id IS NULL
请您尝试执行以下操作：
SELECT distinct book.title,book.subject,author.first_name,author.last_name
FROM book inner join author on book.id = author._id left outer join loaned_books ON book._id = loaned_books.book_id where loaned_books.book_id is null or length(loan_date) = 0 or loan_date = ''