0
 create procedure fine()
 begin
 declare a integer(10);
 declare b integer(10);
 declare c integer(10);
 declare d integer(10);
 declare cal_fine cursor for select book_id,datediff(return_date,issue_date) 
from return_book where datediff(return_date,issue_date)>10;
open cal_fine;
myloop:loop
fetch cal_fine into a,b;
set c=(b-10)*5;
update return_book set fine=c where book_id=a;
end loop;
close cal_fine;
end;

I have this code to find fine but the problem is that my issue_date is not in return_book table so I have to join issue_book and return_book But then how do I use datediff function? Plz help me

N97
  • 43
  • 1
  • 2
  • 9
  • What does your code look like with the join to issue_book? – P.Salmon Oct 08 '17 at 07:40
  • @P.Salmon declare cal_fine cursor for select ib.issue_date,rb.return_date from return_book rb,issue_book ib where rb.book_id=ib.book_id But then what do I do about datediff? this? : datediff(return_date,issue_date) from return_book where datediff(return_date,issue_date)>10; – N97 Oct 08 '17 at 07:48
  • If your question is Do I need to repeat the datediff calculation in the where clause? the answer is yes you do because the where clause comes before the select in the order of execution see https://stackoverflow.com/questions/24127932/mysql-query-clause-execution-order. You should also change the implicit join to and explicit join. – P.Salmon Oct 08 '17 at 08:18
  • @P.Salmon Can you please write the query for it? – N97 Oct 08 '17 at 08:34

1 Answers1

1

Given drop table issue_book,return_book;

    create table issue_book (book_id int, issue_date date);
    create table return_book (book_id int, return_date date);

    insert into issue_book values(1,'2017-01-01'), (2,'2017-09-02'),(3,'2017-09-01');
    insert into return_book values(1,'2017-10-08'), (2,'2017-10-08');

This Query

select ib.issue_date,rb.return_date ,datediff(return_date,issue_date) ddiff
from return_book rb
join issue_book ib on rb.book_id=ib.book_id 
where datediff(return_date,issue_date)>10;

Results in

+------------+-------------+-------+
| issue_date | return_date | ddiff |
+------------+-------------+-------+
| 2017-01-01 | 2017-10-08  |   280 |
| 2017-09-02 | 2017-10-08  |    36 |
+------------+-------------+-------+
2 rows in set (0.00 sec)

But there may be a flaw in your logic namely what happens if a book has not been returned - there will be no entry in return_books.

Also not need for a procedure or cursor this can be achieved with an update statement like

update return_book rb join issue_book ib on rb.book_id=ib.book_id 
 set fine = (datediff(return_date,issue_date) - 10) * 5
 where datediff(return_date,issue_date)>10;
P.Salmon
  • 17,104
  • 2
  • 12
  • 19
  • Thank you so much!! yeah update would be much easier but they have specifically told us to use procedure in our college but anyways your answer was really helpful..thanks again!! – N97 Oct 08 '17 at 09:53