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 ?
2
Answers
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:
LEFT JOIN
(to capture non-borrowed books too)DateReturn IS NULL
If we take these considerations into account, we can follow these steps to get our solution:
COUNT
aggregate function will output 0 if the value is NULL (no book was borrowed)Check the demo here.
This looks like a good spot for a lateral join (available starting MySQL 8.0.14):
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 – or0
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.