Joining three tables. Two related, and one unrelated to the other two

Question

I want to Create a view called ShowAllBooksBorrowed that shows the information (BookName, ISBN, BookCategoryName, BookAuthor, BorrowedDate, ReturnDate, ActualReturnDate) of all the books that have been borrowed from the library but not returned.

I am having trouble figuring out how to relate the BorrowerInfo table to the BookInfo and BookCategory table using the information from the Books table. I understand how it relates but I am not sure how to carry this out. I think I may need to use Union or CrossJoin?

here is my database:

Answer 1

You could create a view like as shown below:

create view show_all_books_borrowed as 
select
  k.bookname,
  b.isbn,
  c.bookcategoryname,
  k.bookauthor, 
  w.borrowdate, 
  w.returndate, 
  w.actualreturndate
from books b
join borrowerinfo w on w.bookid = b.bookid
join bookinfo k on k.isbn = b.isbn
join bookcategory c on k.bookcategoryid = c.bookcategoryid
where w.actualreturndate is null