All we need is an easy explanation of the problem, so here it is.
Let’s say I have a
Library database and the following tables in it:
Books(ID, Title, Author, PublihserID, LanguageCode, Genre, DatePublished, ISBN)– contains examples of books as abstract items, intellectual work
|1||Death On The Nile||Agatha Christie||2||EN||Novel||1937-11-01||4215574186436|
Branches(ID, Name, City, Address)– contains the branches of the library in different cities
BookCopies(ID, BookID, BranchID, Condition)– contains books as physical items and what branch they are located in
I want to write a query, which for each Book in
Books displays how many physical copies of the book there are in each branch of the library.
So far my data in the database has three Branches with IDs
I have written the following query, which properly displays what I want (with some added extra such as filtering by author Agatha Christie):
SELECT Books.Title, Books.PublisherID, Books.DatePublished, Books.ISBN, r1.Branch1, r1.Branch2, r1.Branch3 FROM Books JOIN (SELECT copies.BookID, br1.Branch1, br2.Branch2, br3.Branch3 FROM BookCopies copies JOIN (SELECT BookCopies.BookID, COUNT(BookCopies.BranchID) AS Branch1 FROM BookCopies GROUP BY BookCopies.BookID, BookCopies.BranchID HAVING BookCopies.BranchID=1) br1 ON copies.BookID=br1.BookID FULL JOIN (SELECT BookCopies.BookID, COUNT(BookCopies.BranchID) AS Branch2 FROM BookCopies GROUP BY BookCopies.BookID, BookCopies.BranchID HAVING BookCopies.BranchID=2) br2 ON copies.BookID=br2.BookID FULL JOIN (SELECT BookCopies.BookID, COUNT(BookCopies.BranchID) AS Branch3 FROM BookCopies GROUP BY BookCopies.BookID, BookCopies.BranchID HAVING BookCopies.BranchID=3) br3 ON copies.BookID=br3.BookID GROUP BY copies.BookID, br1.Branch1, br2.Branch2, br3.Branch3) r1 ON Books.ID=r1.BookID WHERE Books.Author='Agatha Christie'
|Death on the Nile||2||1937-11-01||4215574186436||2||3||1|
But there are two fundamental problems with such a query:
- I am sure it’s really slow, ineffective and full of bad practices
- It is hardcoded for three branches. It wouldn’t work if the library opened a fourth, fifth etc branch.
How can I improve my query so that it works for all branches (no matter their count) and preferrably uses resources efficiently?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Basically, you don’t. Formatting is a job for the presentation layer, in the database layer you focus on getting the correct results:
SELECT BookID, BranchID, COUNT(*) FROM BookCopies GROUP BY BookID, BranchID
Gives you the counts for each book/branch combination. Join with Books and Branches:
SELECT b.Title , b.PublisherID , b.DatePublished , b.ISBN , r.Branch , x.CNT FROM Books b JOIN ( SELECT BookID, BranchID, COUNT(*) AS CNT FROM BookCopies GROUP BY BookID, BranchID ) AS x USING (BookID) JOIN Branches r USING (BranchID) ORDER BY b.BookID, r.BranchID
In your application, you can loop over that resultset. When bookid changes you make a new row in your "report". for every new branchid you make a new column
EDIT: SQL server does not support USING clause:
SELECT b.ID as BookID , b.Title , b.PublisherID , b.DatePublished , b.ISBN , r.ID as BranchID , r.Name as Branch , x.CNT FROM Books AS b JOIN ( SELECT BookID, BranchID, COUNT(*) AS CNT FROM BookCopies GROUP BY BookID, BranchID ) AS x ON b.ID = x.BookID JOIN Branches AS r ON r.ID = x.BranchID ORDER BY b.ID, r.ID;
According to the comment, this is a course project looking for ideas. Some things that might be worth investigating are:
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂