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

Question

Let me show the table structure

Book Table

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++'

--SELECT * FROM @Book

BookAuthors Table

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'

--SELECT * FROM @BookAuthors

BookCategories Table

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 * FROM @BookCategories

By using these tables, I want to search books by BookID, AuthorID and CategoryID; For AuthorID and CategoryID search, I need to join with BookAuthors and BookCategories table respectively. But When I try to search by AuthorId, the result gives me same BookId multiple times.

Query

SELECT BK.BookID, BA.AuthorID, BC.CategoryID 
FROM @Book BK
LEFT JOIN @BookAuthors BA ON BA.BookID = BK.BookID
LEFT JOIN @BookCategories BC ON BC.BookID = BK.BookID
WHERE BA.AuthorID = 1

Result

BookID  ----- AuthorID ----- CategoryID 
   1             1               1
   1             1               2
   2             1               1
   2             1               3

I think, Till Now It is good; Now, I want to generate the result like following:

BookID  ----- AuthorIDs ----- CategoryIDs
   1             1,2             1,2
   2             1,3             1,3

Any Idea?

Answer 1

I am not sure why you want your results to output like that, and I do not think it is a good idea.

But you can do it using the stuff() with select ... for xml path ('') method of string concatenation.

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
  )

rextester demo: http://rextester.com/CVYKM71668

returns:

+--------+-----------+-------------+
| BookId | AuthorIds | CategoryIds |
+--------+-----------+-------------+
|      1 | 1,2       | 1,2         |
|      2 | 1,3       | 1,3         |
+--------+-----------+-------------+