clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798667 fiddles created (41910 in the last week).

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);
 hidden batch(es)


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
 hidden batch(es)


create table Branches (ID int not null primary key , Name varchar(255) not null , City varchar(255) not null , Address varchar(255) not null);
 hidden batch(es)


insert into Branches(ID, Name, City, Address) values (1, 'Name1', 'London', 'Address1') , (2, 'Name2', 'Birmingham', 'Address2') , (3, 'Name3', 'Manchester', 'Address3');
3 rows affected
 hidden batch(es)


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));
 hidden batch(es)


insert into BookCopies (ID, BranchID) values (1,1), (2,1), (3,2), (4,2), (5,2), (6,3);
6 rows affected
 hidden batch(es)


SELECT BookID, BranchID, COUNT(*) FROM BookCopies GROUP BY BookID, BranchID;
BookID BranchID (No column name)
1 1 2
1 2 3
1 3 1
 hidden batch(es)


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
 hidden batch(es)