SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Books
( ID int not null primary key
, Title varchar(255) not null
, Author varchar(255) not null
, PublisherID int not null
, LanguageCode char(2) not null
, Genre varchar(10) not null
, DatePublished date not null
, ISBN varchar (255) not null);
insert into books (ID, Title, Author, PublisherID, LanguageCode, Genre, DatePublished, ISBN)
values (1, 'Death On The Nile', 'Agatha Christie', 2, 'en', 'Novel', '1937-11-01', '4215574186436');
1 rows affected

create table Branches
(ID int not null primary key
, Name varchar(255) not null
, City varchar(255) not null
, Address varchar(255) not null);
insert into Branches(ID, Name, City, Address)
values (1, 'Name1', 'London', 'Address1')
     , (2, 'Name2', 'Birmingham', 'Address2')
     , (3, 'Name3', 'Manchester', 'Address3');
3 rows affected

create table BookCopies
(ID int not null primary key
, BookID int default 1 not null references books (ID)
, BranchID int not null references branches (ID));
insert into BookCopies (ID, BranchID)
values (1,1), (2,1), (3,2), (4,2), (5,2), (6,3);
6 rows affected

SELECT BookID, BranchID, COUNT(*)
FROM BookCopies
GROUP BY BookID, BranchID;


BookID
BranchID
(No column name)


1
1
2

1
2
3

1
3
1


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


BookID
Title
PublisherID
DatePublished
ISBN
BranchID
Branch
CNT


1
Death On The Nile
2
1937-11-01
4215574186436
1
Name1
2

1
Death On The Nile
2
1937-11-01
4215574186436
2
Name2
3

1
Death On The Nile
2
1937-11-01
4215574186436
3
Name3
1

BookID	Title	PublisherID	DatePublished	ISBN	BranchID	Branch	CNT
1	Death On The Nile	2	1937-11-01	4215574186436	1	Name1	2
1	Death On The Nile	2	1937-11-01	4215574186436	2	Name2	3
1	Death On The Nile	2	1937-11-01	4215574186436	3	Name3	1