0

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
ID Title Author PublisherID LanguageCode Genre DatePublished ISBN
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
ID Name City Address
1 Name1 London Address1
2 Name2 Birmingham Address2
3 Name3 Manchester Address3
  • BookCopies(ID, BookID, BranchID, Condition) - contains books as physical items and what branch they are located in
ID BookID BranchID Condition
3aa99df2-7a88-4ca8-a965-046f478ac9f3 1 1 Poor
34beeffa-14c9-4796-a61c-0477be59af0f 1 1 Excellent
1dc0e7cd-0f9e-42b2-829a-04de9a77ae47 1 2 Average
88f0045c-3910-4fd6-9a29-078c2d48bfb8 1 2 Good
ea3aafe9-0ada-4396-9ed3-0867912b7958 1 2 Poor
6d003fd5-83e7-4df5-9aa8-08dd61d53eb2 1 3 Excellent
... ... ... ...

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 1, 2 and 3. 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'
Title Publisher DatePublished ISBN Branch1 Branch2 Branch3
Death on the Nile 2 1937-11-01 4215574186436 2 3 1
... ... ... ... ... ... ...

But there are two fundamental problems with such a query:

  1. I am sure it's really slow, ineffective and full of bad practices
  2. 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?
8
  • 1
    "Branches" are rows in your database. To turn rows into columns you pivot. To do this for any number of branches you must dynamically pivot. "I am sure it's really slow" How do you know? Just "looking" at a query isn't measuring. Don't assume. I will suggest that you don't actually know your query works correctly. Do you have a situation where a book is in one location but not in others? If not, you don't know. And I suggest you start learning and using best practices.
    – SMor
    May 14 at 12:55
  • @SMor Well, I didn't have a book that was available only in one branch. I added one such example and the result was NULL for the other two branches, which isn't "0", but works well enough. My assumption that the query is slow is based on the fact that it involves several joins and multiple very similar subqueries and is also the slowest to execute of all 10 that I have written. As for pivoting, I haven't studied that yet, so it's something new, which I am curious to learn about. Thanks. May 14 at 15:02
  • @SMor I researched Pivoting and so far I have this: DECLARE @branches AS nvarchar(MAX)=(SELECT STRING_AGG(Branches.ID, ',') FROM Branches); SELECT * FROM (SELECT BookCopies.BookID, BookCopies.BranchID, COUNT(*) AS [Count] FROM BookCopies GROUP BY BookCopies.BookID, BookCopies.BranchID ) SourceTable PIVOT ( SUM([Count]) FOR [BranchID] IN ([1],[2],[3],[4]) ) AS PivotTable; It works with [1],[2], etc but it doesn't work if I put @branches in :Incorrect syntax near ''@branches''. Expecting '.', ID, or QUOTED_ID. May 14 at 16:45
  • I fixed it. So it seems that in order to do dynamic pivoting, I have to create a query like this: DECLARE @branches AS nvarchar(MAX); SET @branches=''; SELECT @branches+= STRING_AGG(CONCAT('[',Branches.ID,']'),',') FROM Branches; DECLARE @q AS nvarchar(MAX); SET @q='SELECT * FROM (SELECT BookCopies.BookID, BookCopies.BranchID, COUNT(*) AS [Count] FROM BookCopies GROUP BY BookCopies.BookID, BookCopies.BranchID ) SourceTable PIVOT ( SUM([Count]) FOR [BranchID] IN ('+@branches+') ) AS PivotTable;'; EXEC(@q) May 14 at 17:10
  • 1
    I'm talking about adding [] to quote column names. This is not correct because you may not be escaping them properly (for example you may have a [ in the text you are trying to escape). Instead you should use QUOTENAME. In your new version you would have DECLARE @cols AS nvarchar(MAX) = (SELECT STRING_AGG(CONCAT( 'ISNULL(', QUOTENAME(Branches.ID), ',0) AS ' + QUOTENAME(CONCAT('Branch ', Branches.ID)), ')' ), ', ') FROM Branches); May 16 at 8:16

1 Answer 1

1

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:

Fiddle

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:

STRING_AGG

Window functions

GROUP BY ROLLUP, CUBE, GROUPING SETS

CTE, Common Table Expressions

Temporal tables

5
  • As a person, who has experience with Java, I agree it would be easier to do this in the representation layer. The reason I am aiming for this is that this is part of a course project and my task is to write 10 grouping and aggregation queries, so I wanted to make something complicated, not just 5-liners. As for the USING keyword, this is MySQL syntax, isn't it? May 14 at 8:33
  • No using is standard sql, it worls with mysql, mariadb, postgres, ibm db2 and probably most others as well (dont know for sqlserver, missed your tag, sorry). Ideas for your project: grouping sets, groub by cube, window functions. Temporal tables.
    – Lennart
    May 14 at 8:56
  • From what I tried and researched, MS SQL Server doesn't seem to recognise the USING keyword ( why? :-/ ) but that's no problem because it's easily replaceable with JOIN ON. Thanks for the ideas. May 14 at 9:29
  • 1
    You are right about USING, I added a fiddle and some links
    – Lennart
    May 14 at 10:10
  • 1
    Thanks for the links. A user posted a comment suggesting that if I really wanted to get the data like this in sql, I should do pivoting. I managed to write a query with pivoting, which uses the STRING_AGG function you suggested. I just wanted to say thanks again. May 14 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.