sql - 避免使用带有 SQL 查询的 View

Question

我有以下查询:

Create View Temp2 as 
(SELECT CurrentLoan.memberID, bookID, due_date 
 FROM CurrentLoan, Member
 WHERE CurrentLoan.memberID = Member.memberID 
 AND Member.firstname ="John" 
 AND Member.lastname = "Smith");

SELECT Temp2.bookID, Book.title, Temp2.due_date 
FROM Temp2, Book 
WHERE Temp2.bookID = Book.bookID ;

但是，我需要能够在不使用任何 View 的情况下生成相同的结果，并且全部在一个语句中。关于如何进行此设置有什么建议吗？

Answer 1

只需使用JOIN。我假设 bookID 来自 CurrentLoan

SELECT CurrentLoan.bookID, Book.title, CurrentLoan.due_date
FROM CurrentLoan
JOIN Member ON CurrentLoan.memberID = Member.memberID
JOIN Book ON CurrentLoan.bookID = Book.bookID 
WHERE Member.firstname ="John" AND Member.lastname = "Smith"