2

I have three table:

  1. book_shelf -> pkid and other columns

  2. book_author -> pkid,fkid_book_shelf,fkid_author

  3. author -> pkid,name, etc.

For a row in book_shelf in want to get author names as comma separated. If a bookshelf entity has two author then two row is inserted in book_author table.

Here is what I tried

(select BookShelf.pkid as SrNo,stuff((select ',' + a.name
    from 
    lib.book_shelf bs inner join
    lib.book_author ba on bs.pkid=ba.fkid_book_shelf
    inner join lib.author a on a.pkid=ba.fkid_authors 
    for xml path ('')
), 1, 1, '') as Author from lib.book_shelf BookShelf)

But it selects all the author name for every book_shelf row.

JasonMArcher
  • 14,195
  • 22
  • 56
  • 52
Mir Gulam Sarwar
  • 2,588
  • 2
  • 25
  • 39

1 Answers1

1

hope this might help you.

CREATE TABLE BookShelf(ID INT)
CREATE TABLE BookAuthor(ID INT, BookShelf INT, AuthorID INT)
CREATE TABLE Author(ID INT, Name VarChar(50))
GO

INSERT INTO BookShelf VALUES(1)
INSERT INTO BookShelf VALUES(2)

INSERT INTO Author VALUES(1,'Author1')
INSERT INTO Author VALUES(2,'Author2')
INSERT INTO Author VALUES(3,'Author3')
INSERT INTO Author VALUES(4,'Author4')


INSERT INTO BookAuthor VALUES(1,1,1)
INSERT INTO BookAuthor VALUES(1,1,2)
INSERT INTO BookAuthor VALUES(1,2,3)
INSERT INTO BookAuthor VALUES(1,2,4)

SELECT 
    BookShelf.ID, 
    STUFF((SELECT ',' + Author.Name FROM BookAuthor INNER JOIN Author ON BookAuthor.AuthorID = Author.ID WHERE BookAuthor.BookShelf = BookShelf.ID FOR XML PATH('')),1,1,'')
FROM 
    BookShelf
classic_vmk
  • 495
  • 2
  • 15