Minus 1 unit if the condition is met in mysql-CodePudding

I need some help here, my request is : List the bookID and number of copy of this in Library (Number of copy in library = number of copy – number of copy student borrow but not return yet)

and this is my code

Select lms.books.bookid,
       (lms.books.numofcopy-1 where giveback = 0) as numberofbookremain
From lms.books 
join lms.receipts On lms.books.bookid = lms.receipts.bookid

here the data

"LMS Database Script.sql":

-- create the database
DROP DATABASE IF EXISTS LMS;
CREATE DATABASE LMS;

-- select the database
USE LMS;

-- Create the tables Categories
Create Table Categories(
    CategoryID Char(3) primary key,
    CategoryName Varchar(50),
    Moreinfo Varchar(255)
);

-- Create the tables Book
Create Table Books(
    BookID Char(6) primary key,
    BookName  Varchar(50),
    Publisher Varchar(50),
    Author Varchar(50),
    CategoryID Char(3),
    Numofpage Int,
    Maxdays int,
    NumOfCopy int,
    Summary varchar(250),
    CONSTRAINT book_fk_category
    FOREIGN KEY (categoryID)
    REFERENCES categories (categoryID)
);

-- Create the tables Students
Create Table Students(
    CardID Char(8) primary key,
    StudentName Varchar(50),
    Address varchar(255),
    Tel Char(11)
);

-- Create the tables Receipts
Create Table Receipts(
    ReceiptID int primary key,
    CardID Char(8),
    BookID Char(6),
    DateBorrow Date,
    DateReturn Date,
    GiveBack bit,
    CONSTRAINT receipts_fk_student
    FOREIGN KEY (CardID)
    REFERENCES students (CardId),
    CONSTRAINT receipts_fk_books
    FOREIGN KEY (BookID)
    REFERENCES books (BookID)
);

-- Insert Data to Categories Tables
Insert Into Categories(CategoryID, CategoryName, Moreinfo)
Values('CSD','Cơ sở dữ liệu','Access, Oracle'),
    ('ECO','Ecommerce','Sách về thương mại điện tử'),
    ('GTT','Giải thuật','Các bài toán mẫu, các giải thuật, cấu trúc dữ liệu'),
    ('HTT','Hệ thống','Windows, Linux, Unix'),
    ('LTT','Ngôn ngữ lập trình','Visual Basic, C, C  , Java'),
    ('PTK','Phân tích và thiết kế','Phân tích và thiết kế giải thuật, hệ thống thông tin v.v..'),
    ('VPP','Văn phòng','Word, Excel'),
    ('WEB','Web','Javascript, Vbscript,HTML, Flash');
    
-- Insert data to Books table
Insert Into Books(BookID,BookName,Publisher,Author,CategoryID,Numofpage,Maxdays,NumOfCopy,Summary)
Values('CSD001','Cơ sở dữ liệu','NXB Giáo dục','Ðỗ Trung Tấn','CSD',200,3,3,'Thiết kế CSDL'),
    ('CSD002','SQL Server 7.0','NXB Ðồng Nai','Elicom','CSD',200,3,2,'Thiết CSDL và sử dụng SQL Server'),
    ('CSD003','Oracle 8i','NXB Giáo dục','Trần Tiến Dũng','CSD',500,5,3,'Từng bước sử dụng Oracle'),
    ('HTT001','Windows 2000 Professional','NXB Giáo dục','Anh Thư','HTT',500,3,2,'Sử dụng Windows 2000'),
    ('HTT002','Windows 2000 Advanced Server','NXB Giáo dục','Anh Thư','HTT',500,5,2,'Sử dụng Windows 2000 Server'),
    ('LTT001','Lập trình visual Basic 6','NXB Giáo dục','Nguyễn Tiến','LTT',600,3,3,'Kỹ thuật lập trình Visual Basic'),
    ('LTT002','Ngôn ngữ lập trình c  ','NXB Thống kê','Tăng Ðình Quý','LTT',500,5,3,'Hướng dẫn lập trình hướng đối tượng và C  '),
    ('LTT003','Lập trình Windows bằng Visual c  ','NXB Giáo dục','Ðặng Văn Ðức','LTT',300,4,3,'Hướng dẫn từng bước lập trình trên Windows'),
    ('VPP001','Excel Toàn tập','NXB Trẻ','Ðoàn Công Hùng','VPP',1000,5,4,'Trình bày mọi vấn đề về Excel'),
    ('VPP002','Word 2000 Toàn tập','NXB Trẻ','Ðoàn Công Hùng','VPP',1000,4,3,'Trình bày mọi vấn đề về Word'),
    ('VPP003','Làm kế toán bằng Excel','NXB Thống kê','Vu Duy Sanh','VPP',200,5,2,'Trình bày phương pháp làm kế toán'),
    ('WEB001','Javascript','NXB Trẻ','Lê Minh Trí','WEB',200,5,2,'Từng bước thiết kế Web động'),
    ('WEB002','HTML','NXB Giáo Dục','Nguyễn Thị Minh Khoa','WEB',100,3,2,'Từng bước làm quen với WEB');
    
-- Insert data to Student table
Insert Into Students(CardID,StudentName,Address,Tel) 
Values('STIT0001','Vy Văn Việt','92-Quang Trung- Đà Nẵng','02363888279'),
    ('STIT0002','Nguyễn Khánh','92-Quang Trung- Đà Nẵng','02363888279'),
    ('STIT0003','Nguyễn Minh Quốc','92-Quang Trung- Đà Nẵng','02363888279'),
    ('STIT0004','Hồ Phước Thoi','92-Quang Trung- Đà Nẵng','02363888279'),
    ('STIT0005','Nguyễn Văn Định','92-Quang Trung- Đà Nẵng','02363888279'),
    ('STIT0006','Nguyễn Văn Hải','92-Quang Trung- Đà Nẵng','02363888279'),
    ('STIT0007','Nguyễn Thị Thuý Hà','92-Quang Trung- Đà Nẵng','02363888279'),
    ('STIT0008','Đỗ Thị Thiên Ngân','92-Quang Trung- Đà Nẵng','02363888279'),
    ('STIT0009','Nguyễn Văn A','30- Phan Chu Trinh- Đà Nẵng','0913576890');
    
-- Insert data to Receipts table
Insert Into Receipts(ReceiptID,CardID,BookID,DateBorrow,DateReturn,GiveBack)
Values(1,'STIT0001','CSD001','2021-07-20',null,0),
    (2,'STIT0001','LTT001','2021-06-30','2021-07-25',1),
    (3,'STIT0002','CSD002','2021-08-15',null,0),
    (4,'STIT0002','LTT003','2021-08-10','2021-08-30',0),
    (5,'STIT0003','WEB001','2021-07-10','2021-07-20',1),
    (6,'STIT0004','HTT001','2021-08-10',null,0),
    (7,'STIT0004','HTT002','2021-08-20','2021-08-25',1),
    (8,'STIT0006','WEB001','2021-08-30',null,1),
    (9,'STIT0006','CSD002','2021-08-10','2021-08-15',1),
    (10,'STIT0006','WEB002','2021-07-15','2021-07-30',1),
    (11,'STIT0007','VPP001','2021-08-30',null,0),
    (12,'STIT0007','VPP003','2021-08-20','2021-08-25',1),
    (13,'STIT0008','VPP001','2021-08-30',null,0),
    (14,'STIT0009','CSD001','2021-08-20','2021-08-23',1)

Can someone help me understand how to do it right ?

CodePudding user response：

In this problem you need to count the number of available copies for each book, and subtract it from the number of currently borrowed copies, which means that the involved tables are "Books" and "Receipts" to be joined together.

We need to do some considerations about it:

not every book is necessarily borrowed by a student at the moment, for this reason, the join between the two tables must be a LEFT JOIN (to capture non-borrowed books too)
not every record for matching books in Receipts must be considered: we want only those books that are "currently borrowed", which can be translated into: "they have not been returned yet" or DateReturn IS NULL

If we take these considerations into account, we can follow these steps to get our solution:

apply a left join from "Books" to "Receipts" tables on matching "BookId" and when "DateReturn" value is NULL
consider "Books.NumOfCopy" as the number of all copies for each book
aggregate on a field of the right table ("Receipts") to count how many copies have been borrowed for each book. Note that the COUNT aggregate function will output 0 if the value is NULL (no book was borrowed)
subtract "Books.NumOfCopy" and the last aggregated field

SELECT b.BookID, 
       b.NumOfCopy,
       COUNT(r.BookID) AS numBorrowedCopies,
       b.NumOfCopy - COUNT(r.BookID) AS numRemainingCopies
FROM      Books b
LEFT JOIN Receipts r
       ON b.BookID = r.BookID
      AND r.DateReturn IS NULL
GROUP BY b.BookID,
         b.NumOfCopy

Check the demo here.

CodePudding user response：

This looks like a good spot for a lateral join (available starting MySQL 8.0.14):

select b.*, r.NumOfBorrowed, b.NumOfCopy - r.NumOfBorrowed as NumRemaining
from Books b
cross join lateral (
    select count(*) NumOfBorrowed 
    from Receipts r 
    where r.BookID = b.BookID and r.DateReturn is null
) r

The lateral join correlates with the Books table; typically, the subquery is executed once for each book, and returns the total count of instances of the book that are borrowed - or 0 if there is none. We can then use that information as we like in the outer query.

If you have a relatively small number of books and a large number of receipts, this should be more efficient than the left join/group by solution.