Join books, authors and books_authors

Transact-SQL
1

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

book_id | author_id

1 | 1
1 | 2
1 | 3
1 | 4
2 | 3
2 | 4

book_id | title | authors

1    |    A    | John, Bush, Alex, Bob
2    |    B    | Alex, Bob
2

You should use FOR XML. If you post your mysql query, we can translate it. or have a go yourself! it's good to learn

3 
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
4

brilliant. Thank you very much.

5

Like @gbritton said, now that you saw the solution, try to reproduce it . This way you learn quicker and better.

6

absolutely. i have to join another table, publisher. That will be my learning curve. With your help i got a tremendous kick start. Thanks again

7

You're welcome!