Merging column values in many to many relationship joins

Question

I have two tables, Books and Authors, with many-to-many relationship between them through a third table called book_authors, i am trying to list all the books with the authors for each book using an inner join in order to display them in a DataList Control, but the join is causing several duplicate rows, because each book may have many authors, so there will be a row for each author.
Example:

book_title           author  
b1                    a1  
b1                    a2

What is the best way to fix this so it becomes:

book_title                author  
b1                        a1, a2

Use a subquery and a string concatenation function on your db, then provide the list of authors as the only string to display. — Gabber, May 11 '12 at 12:19
Take a look at these posts: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005, http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server and http://stackoverflow.com/questions/941103/concat-groups-in-sql-server — Marco, May 11 '12 at 12:21

Arion · Accepted Answer · 2012-05-11T13:28:00.037

Maybe something like this:

SELECT
    Books.book_title,
    STUFF
    (
        (
            SELECT 
                ',' +author
            FROM
                book_authors
                JOIN Authors
                    ON book_authors.authorId=Authors.authorId
            WHERE
                book_authors.bookId=Books.bookid
            FOR XML PATH('')
        )
    ,1,1,'')
FROM
    Books

EDIT

It is hard to say with out you data. Does this work:

DECLARE @Table1 TABLE(ID INT)
DECLARE @Table2 TABLE(Name varchar(100),ID INT)

INSERT INTO @Table1 VALUES(1),(2)
INSERT INTO @Table2 VALUES('test1',1),('test2',1),('test3',2),('test4',2)

SELECT
    t1.ID,
    STUFF
    (
        (
            SELECT 
                ',' +t2.Name
            FROM
                @Table2 AS t2
            WHERE
                t1.ID=t2.ID
            FOR XML PATH('')
        )
    ,1,1,'')
FROM
    @Table1 AS t1

score 1 · Answer 2 · answered May 11 '12 at 12:46

1

If you want to do it purely in SQL then you will need a sub-query that takes the book id and yeilds a list of authors (csv if thats how you want it) as a result. Use that sub-query within another query that for each unique bookid it returns the book_title and author list.

If you don't mind not using pure SQL, I'd say just iterate through the DataList you are currently getting and make a map-type structure (book_title -> authorList) adding the authors as you go through.

The best choice depends on the scope of how your are using this, but generally I'd say the sql route is the way to go.

answered May 11 '12 at 12:46

Andrew Hagner

804
1
9
18

This is exactly what i was going to do, but i can't figure out the SQL query to do that. – Ibrahim Najjar May 11 '12 at 12:57
1

I apologize for not having time to write it all out, but [HERE](http://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/) is a link on how to take a table and make it into a csv string. If you make this into a function then you can use it inside of the query. Simply pass this function the book id, it will return the csv string, and do that for all books. The post below by G. Linoff could work too if you know the max number of authors you have. – Andrew Hagner May 15 '12 at 22:11

score 1 · Answer 3 · answered May 11 '12 at 13:31

What you want to do is string aggregate concatenation. There are some very good posts on this subject.

Here is an alternative, that might work easily in your case, since books do not have too many authors:

select b.name,
       (max(case when authornum = 1 then author else '' end) +
        max(case when authornum = 2 then ', '+ author else '' end) +
        max(case when authornum = 3 then ', '+ author else '' end)
       ) as authors
 from (select ba.*,
              row_number() over (partition by bookid order by authorid) as authornum
       from BookAuthors ba
      ) bajoin
      Authors a
      on a.authorid = ba.authorid join
      Books b
      on b.bookid = ba.bookid
 group by b.name

You simply need to be sure that you include enough authors in the select statement.

thank you, i might try this in the future if i needed to, the above posts worked well for the time being. — Ibrahim Najjar, May 11 '12 at 16:21

Merging column values in many to many relationship joins

3 Answers3