Get Unique Search Data When Join Providing Same Primary Key Value Multiple Times, Sql Server

Language: Layout:

DECLARE @Book TABLE (BookID INT, BookName VARCHAR(30))

INSERT INTO @Book
    (BookID, BookName)
SELECT '1', 'Art of Programming Contest' UNION ALL  
SELECT '2', 'Programming in C' UNION ALL
SELECT '3', 'Programming in C++'

DECLARE @BookAuthors TABLE (BookID INT, AuthorID INT)

INSERT INTO @BookAuthors
    (BookID, AuthorID)
SELECT '1', '1' UNION ALL
SELECT '1', '2' UNION ALL
SELECT '2', '1' UNION ALL
SELECT '2', '3' UNION ALL
SELECT '3', '2' UNION ALL
SELECT '3', '4'

DECLARE @BookCategories TABLE (BookID INT, CategoryID INT)

INSERT INTO @BookCategories
    (BookID, CategoryID)
SELECT '1', '1' UNION ALL
SELECT '1', '2' UNION ALL
SELECT '2', '1' UNION ALL
SELECT '2', '3' UNION ALL
SELECT '3', '2' UNION ALL
SELECT '3', '4'

select 
    bk.BookId
  , AuthorIds = stuff((
  select ','+convert(varchar(12),ba.AuthorId)
    from @BookAuthors ba
    where ba.BookId = bk.BookId
    order by ba.AuthorId
  for xml path (''), type).value('.','nvarchar(max)')
  ,1,1,'')
  , CategoryIds = stuff((
  select ','+convert(varchar(12),bc.CategoryId)
    from @BookCategories bc
    where bc.BookId = bk.BookId
    order by bc.CategoryId
  for xml path (''), type).value('.','nvarchar(max)')
  ,1,1,'')
from @Book bk
where exists (
  select 1
  from @BookAuthors ba
  where ba.BookId = bk.BookId
    and ba.AuthorId = 1
  )

View schema
Execution time: 0,03 sec, rows selected: 6, rows affected: 15, absolute service time: 0,19 sec

Get Unique Search Data When Join Providing Same Primary Key Value Mult...