triple join issues in producing desired outputs-CodePudding

I am a student working on an SQL query to produce unreturned books based on the return date column value being NULL, however the output requires me to join 3 tables. The result obtained in incorrect, and I have tried many combinations of GROUP BY and additional WHERE conditions.

Here is my question prompt:

Write a query to find out what copies have not been returned yet. The output should display book’s title, borrower’s name, copy_id, borrower’s phone, and borrower’s email. In addition, it should also be sorted by borrower’s name.(Hint: loan_date_returned is null)

ER Diagram

My code is as follows:

SELECT BK.book_title AS "Title", CONCAT(BRW.borrower_fname, ' ' ,BRW.borrower_lname) AS "Name", L.copy_id AS "Copy ID", BRW.borrower_phone AS "Phone", BRW.borrower_email AS "Email"
FROM loan AS L, book AS BK, borrower AS BRW
WHERE BRW.borrower_id = L.borrower_id AND L.loan_date_returned IS NULL
ORDER BY BRW.borrower_fname, BRW.borrower_lname

I visually deduce that only 6 results are to be expected from the 20ish entries in the loan table, however, it seems that I am receiving repeats and results where many borrowers have borrowed almost all books which is erroneous.

CodePudding user response：

Good effort, your query was CLOSE. You did not have any condition on how the loan was associated with a book.

Also, you were writing in old ANSI-SQL instead of using proper JOIN syntax. Think of the FROM clause as the section on defining all tables (and their aliases) that WILL be used in the query AND how they are related (typically your ID keys joining them). In the case of loan to book, I am assuming there is some "BookID" that is loaned to the actual book. That was missing.

As you can see by the indentations as I write my query, you can see how table 1 joins to the next table by its immediately following "ON" clause. Join tableX to tableY on WhatCondition. Since a given loaned item is associated with both a book, and who borrowed it, you can see that the indentation of the borrower is at the same level, but has ITs related join "ON" condition immediately following it. I also always write the left-side table first = right side table. I think it helps to know how the field from the first matches to the second.

Your WHERE clause is ok based on the sole-condition that the loan date returned is null.

As for ORDER BY, I have seen times where an engine will nag you if you try to order by some column that is not in the result set of columns selected, so I just copied the concat() for the name into the order by clause.

SELECT 
        BK.book_title Title, 
        CONCAT(BRW.borrower_fname, ' ' , BRW.borrower_lname) Name, 
        L.copy_id AS "Copy ID", 
        BRW.borrower_phone Phone, 
        BRW.borrower_email Email
    FROM 
        loan L
            JOIN Copies C
               on L.copy_id = C.Copy_id
               JOIN book BK
                  ON C.Book_ID = BK.Book_ID
            JOIN borrower BRW
                ON L.borrower_id = BRW.borrower_id
    WHERE 
        L.loan_date_returned IS NULL
    ORDER BY 
        CONCAT(BRW.borrower_fname, ' ' , BRW.borrower_lname)