Display count of rows from one table in columns in another without knowing how many columns there would be



  • 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?


  • 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:

    https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=987f6ed2b063b01d0a28ab81e1285197

    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:

    https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15

    https://www.sqlservertutorial.net/sql-server-window-functions/

    https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15

    https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

    https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2