Stored procedure author/books and IsUnique

Transact-SQL
1

Hello,
I have a database solution for a book library.
Putting simply, I have a Authors table:

AuthorID INT NOT NULL
FirstName varchar(50) NOT NULL
LastName varchar(50) NOT NULL

a book table:

BookId INT NOT NULL
Title varchar(200) NOT NULL
EditorID INT NOT NULL

and a many-to-many table for AuthorsBooks

AuthorId INT NOT NULL
BookId INT NOT NULL
Order INT NOT NULL

where "Order" means if the author is the first/second/etc in the book's authors list.

Now I have to write a stored procedure that gets me the list of books passing the authorId as parameter
and also a boolean flas (IsUnique for example) in the case the author is the only author of the book.

How can I write a stored like this?

Thanks in advance.

Luigi

2

Key point for isUnique field is COUNT(1) OVER(PARTITION BY AB.BookId) AS no_Authors

CREATE OR ALTER PROCEDURE dbo.Books_ByAuthor_Get
(
    @i_AuthorID INT 
)
AS 
BEGIN

;WITH cte_AB
AS
(SELECT AB.BookId,AB.AuthorID
                ,COUNT(1) OVER(PARTITION BY AB.BookId) AS no_Authors
        FROM dbo.AuthorsBooks AS AB 
)
        
SELECT B.Title , B.BookId
    ,CASE WHEN AB.no_Authors = 1 THEN 1 ELSE 0 END as isUnique
FROM
   dbo.Book as B
   INNER JOIN  cte_AB as AB
        ON B.BookId = AB.BookId
   INNER JOIN dbo.Authors AS A
       ON A.AuthorId = AB.AuthorID
WHERE
   AB.AuthorID = @i_AuthorID
END

EXEC dbo.Books_ByAuthor_Get @i_AuthorID = 1
output:

Title isUnique
AA Book 0
Book AZ 0
BBBB Cook 1

dbfiddle here

the sample I used:

create table dbo.Authors (
AuthorID INT NOT NULL
,FirstName varchar(50) NOT NULL
,LastName varchar(50) NOT NULL
,CONSTRAINT pk_Authors_AuthorID PRIMARY KEY CLUSTERED(AuthorID)
)

create table dbo.Book
(
BookId INT NOT NULL
,Title varchar(200) NOT NULL
,EditorID INT NOT NULL
,CONSTRAINT pk_Book_BookId PRIMARY KEY CLUSTERED(BookId)
)

create table dbo.AuthorsBooks
(
AuthorId INT NOT NULL
,BookId INT NOT NULL
,[Order] INT NOT NULL
,CONSTRAINT PK_AuthorsBooks_AuthorID_BookId PRIMARY KEY CLUSTERED(AuthorId ,BookId )
)

INSERT INTO dbo.Authors(AuthorID,FirstName,LastName)
VALUES(1,'AA','ABBCC') ,(2,'ZZ','yyyy')

INSERT INTO dbo.Book(BookId ,Title ,EditorID)
VALUES(1,'AA Book',1) , (2,'Book AZ',1) , (3,'BBBB Cook',1), (4,'DZZZ Cook',1)

INSERT INTO dbo.AuthorsBooks(AuthorID,BookId,[Order])
VALUES(1,1,1),(1,2,1),(1,3,1)
    ,(2,1,2),(2,2,2),(2,4,1);
2 Likes
3

For isUnique field you can use also : MAX(AB.AuthorID) OVER(PARTITION BY AB.BookId) AS max_Authors and then test if this new expression is equal to 1
CASE WHEN max_Authors = 1 THEN 1 ELSE 0 END

1 Like
4

Great, thank you Stepson.

Luigi

1 Like
5

I don't think it needs to be that complex:

DECLARE @AuthorId int
SET @AuthorId = 1
;WITH cte_get_books_by_author AS
(
    SELECT book_list.BookId, COUNT(*) AS AuthorCount
    FROM (
        SELECT BookId
        FROM dbo.AuthorsBooks
        WHERE AuthorId = @AuthorId
    ) AS book_list
    INNER JOIN dbo.AuthorsBooks ab ON ab.BookId = book_list.BookId
    GROUP BY book_list.BookId
)
SELECT cgb.BookId, b.Title, 
    CASE WHEN AuthorCount = 1 THEN 1 ELSE 0 END AS IsOnlyAuthor
FROM cte_get_books_by_author cgb
INNER JOIN dbo.Books b ON b.BookId = cgb.BookId
2 Likes