Id like to join the 3 tables and get one record per book even when the book had multiple authors. I found an answer for MySQL (GROUP_CONCAT) but i'm using SQL 2012
Thanks for any suggestion.
Books
book_id | title
1 | A
2 | B
Authors
author_id | name
1 | John
2 | Bush
3 | Alex
4 | Bob
BooksAndAuthors
SELECT
B.book_id ,
B.title ,
STUFF(CA.authors,1,1,'') AS authors
FROM
dbo.Books AS B
CROSS APPLY
(SELECT
','+A.name
FROM
dbo.BooksAndAuthors AS BA
INNER JOIN dbo.Authors AS A
ON BA.author_id =A.author_id
WHERE
BA.book_id = B.book_id
FOR XML PATH(''))CA(authors)
Output for this query:
book_id title authors
1 A John,Bush,Alex,Bob
2 B Alex,Bob