CODEWITHSUNDEEP
mysqlsqldatetime
Serg Shapoval
Serg Shapoval

Reputation: 717

Can't make valid SQL request. MySQL

I need to make a request that needed to take all people whose library card ends in this month and who has not returned books to the library. I have made a request that finding people whose library card ends in this month: 

select reader.nameReader from reader where year(reader.validityOfTicket)=year(current_date()) and month(reader.validityOfTicket)=month(current_date())
and day(reader.validityOfTicket)>day(current_date())

But not i can't take all people that has't returned books to the library. If somebody took a book, it shows in history.actionHistory like 'Give'. If somebody brought back a book, it shows in history.actionHistory like 'Take'. Here is my tables. Help pls!

        create table author(
idAuthor int auto_increment NOT NULL,
nameAuthor VARCHAR(30) NOT NULL,
surnameAuthor VARCHAR(40),
PRIMARY KEY(idAuthor)
);

create table book(
idBook int NOT NULL,
title varchar(20) NOT NULL,
publishingHouse varchar(60) NOT NULL,
publishingDate DATE NOT NULL,
location VARCHAR(30) NOT NULL,
accessType enum('Full','Restricted') NOT NULL,
currentState enum('Store','Reader'),
idAuthor int NOT NULL ,
PRIMARY KEY(idBook),
foreign Key (`idAuthor`) references `author`(`idAuthor`)
 ON DELETE CASCADE ON UPDATE CASCADE

);
create table reader(
idReader int auto_increment NOT NULL,
surnameReader varchar(30) NOT NULL,
nameReader varchar(30) NOT NULL,
patronymicReader varchar(30) NOT NULL,
validityOfTicket DATE NOT NULL,
category enum('Full', 'Restricted') NOT NULL,
PRIMARY KEY(idReader)
);

create table history(
idHistory int auto_increment NOT NULL,
dateHistory DATE NOT NULL,
actionHistory enum('Give','Take'),
idReader int NOT NULL,
idBook int NOT NULL,
PRIMARY KEY(idHistory),
foreign key (`idReader`) REFERENCES `reader`(`idReader`)
ON DELETE CASCADE ON UPDATE CASCADE
);

Upvotes: 2

Views: 59

Answers (1)

O. Jones
O. Jones

Reputation: 108816

For starters, you can use MySQL's LAST_DAY() function, like this,

     reader.validityOfTicket >= LAST_DAY(CURRENT_DATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH
 AND reader.validityOfTicket <  LAST_DAY(CURRENT_DATE()) + INTERVAL 1 DAY

to find the tickets expiring this month. If you do this you'll be able to use an index on your validityOfTicket column to speed up your query.

Then you must, for each library patron, count the books Given but not Taken. Something like this might work. 

         SELECT idReader,
                SUM( CASE WHEN actionHistory = 'Give' THEN 1
                          WHEN actionHistory = 'Take' THEN -1
                          ELSE 0 END ) booksGiven
           FROM history
          GROUP BY idReader

You can then treat that summary query as a subquery and join it to the other.

select reader.nameReader
  from reader
  join (
       SELECT idReader,
              SUM( CASE WHEN actionHistory = 'Give' THEN 1
                        WHEN actionHistory = 'Take' THEN -1
                        ELSE 0 END ) booksGiven
         FROM history
        GROUP BY idReader
       ) books on reader.readerId = books.readerId
 where reader.validityOfTicket >= LAST_DAY(CURRENT_DATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH
   and reader.validityOfTicket < LAST_DAY(CURRENT_DATE()) + INTERVAL 1 DAY
   and books.booksGiven > 0

You have one criterion on readers: ticket expiring this month. You have another criterion on books: more than one checked out. The trick is to get each of those queries working separately, then to join them.

Upvotes: 1

Related Questions