SELECT statement isn't returning any results when inner-joining three tables and selecting one column from two of them

Question

Goal: Write a query that will provide a list of Patrons who have borrowed books, and the books they have borrowed.

• Book: book_id, title, author_first
• Patron: name, phone, card_number
• Loan: card_number, Book_id, date_out, date_due

Here is the code I've come up with:

select 
    Patron.name,
    Book.title
from 
    Patron
inner join 
    Loan on Patron.Card_Number = Loan.card_number
inner join 
    Book on Loan.book_ID = Book.book_id
where 
    Loan.date_out is not null;

It returns the correct columns but it doesn't return any data.

I'm using SSMS with SQL Server 2017 installed. I want it to reference the card number in the Patron table against the Loan table to see that this card number has taken a loan out, then select out the name of that book based on the book ID.

Answer 1

Your loan.card_number is not same as Patron.Card_Number - that's why it is not showing any records One way could be - cast loan.card_number to int like below -

select Patron.name,
    Book.title
    from Patron
        Inner Join Loan on Patron.Card_Number=cast(Loan.card_number as int)
        Inner Join Book on Loan.book_ID=Book.book_id
    where Loan.date_out is not null