Hello all,
I have three tables: Book, author and Book_Author. The third table make them one to many relation.One book may have more than one authors. They have the columns:
Book: BookID, Title
Author: ID, AuthorNameFirst,AuthorLastName, AuthorName
Book_Author: ID, BOOKID, AuthorID, AuthorPriority
I have a stored procedure which displays title and it's associate authors:
ALTER PROCEDURE [dbo].[Item_Author]
AS
DECLARE @tblBookAuthor table
(rowId int identity(1,1),
BookID nvarchar(10),
Title varchar(50),
Authors varchar(500)
)
Declare @BookID varchar(10),
@Title varchar(200),
@Authors varchar(500),
@Author varchar(100)
DECLARE Book CURSOR FAST_FORWARD FOR
SELECT BookID, Title from Book
Order by Title
OPEN Book
WHILE 1=1
BEGIN
FETCH NEXT FROM Book INTO @BookID, @Title
IF @@FETCH_STATUS <>0
BREAK
SET @Authors=''
set @Author=''
DECLARE BookAuthor CURSOR FAST_FORWARD FOR
SELECT AuthorName from Author a join BookAuthor ba on a.AuthorID=ba.AuthorId where ba.BookId=@BookID
OPEN BookAuthor
WHILE 1=1
BEGIN
FETCH NEXT FROM BookAuthor INTO @Author
IF @@FETCH_STATUS <>0
set @Authors=@Authors + ' , ' + @Author
END
CLOSE BookAuthor
DEALLOCATE BookAuthor
insert into @tblBookAuthor(BookID,Title,Authors) values(@BookID,@Title,@Authors)
END
CLOSE Book
DEALLOCATE Book
Select * From @tblBookAuthor
return
I want to display the authorname depends on AuthorPriority. If the authorPriority has one, it should display lastname, firsname + other authors + 'and' last author:
Let say, title 'SQL QUery' has three authors in the orderPriority 1,2,3:
John michael, hilly clinton, Jorge buss
then it should display:
Michael, John, Hilly Cilton and Jorge buss.
How can I check if authorPriority =1 set this + remianing other and last authors ??
How can i change my above procedure for this output ??
Thanks