CODEWITHSUNDEEP
mysqlsqldatabasedatetime
Robert Ross
Robert Ross

Reputation: 1189

Comparing two datetime fields in a table and displaying a record in another table based on the result

I am trying to compare the to datetime fields in my loan table so if the difference between the dates is bigger then 31 days I can get the names from the borrowers table of the borrowers, who delayed the return of their book. I am not allowed to use triggers or stored procedures for the task, so I am aiming to do it in another way, but I don't even know where to start. I'd appreciate any tips on how to do that.

Here is the part of my database that is used for the task.

enter image description here

Upvotes: 0

Views: 43

Answers (2)

Luke Miller
Luke Miller

Reputation: 36

It looks like you should be able to join the loan table and borrower table and use a WHERE clause. Try this:

SELECT 
    *
FROM borrower B
JOIN loan L 
ON L.Borrower_idBorrowerPK=B.idBorrowerPK
where datediff(L.dateReturned,L.dateBorrowed)>31

Upvotes: 1

McNets
McNets

Reputation: 10807

This sentence should do the work:

SELECT
    borrower.idBorrowerPK,
    borrower.name
FROM
    loan
    left join borrower
        on load.Borrower_idBorrowerPK = borrower.idBorrowerPK
WHERE
    DATEDIFF(CURDATE(), loan.dateBorrowed) >= 31

Upvotes: 1

Related Questions