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?