Duplicate Records avoid while joining the tables

Question

Hi,

I have three tables: Book, author and Book_Author. The third table make them one to many relation.One book may have more than one authors. Book table has Title column and Author table has AuthorName Column.

Book: BookID, Title

Author: ID, AuthorName

Book_Author: ID, BOOKID, AuthorID

I wrote a stored procedure to combine the titles with it's authors and When I

use just this:

SELECT BookID, Title from Book
Order by Title

it works fine but I have to call this procedure from c# with the input author and I changed this query to:

select B.BookID, B.Title, a.authorName from Book b join book_author ba on

b.bookID = ba.bookID join author a on

ba.authorID = a.ID

then it displays the duplicate records into title column for different authors.

Can I just display single title but multiple authors ??

Any help is highly appreciated.

Thanks

Answer 1

No, that's just how join works. You have two choices:

1) Don't worry about it. It's not like it's a huge amount of data overhead, and you can read the book title from any of the rows in your C# app without it being a big deal.

2) Return the book details in one result set, and the author details in the next result set. You can use the NextResult method of SqlDataReader to get to the second result set.

I would do number 2, personally, just because it gets you into a good habit if you end up returning a lot more data next time.

Answer 2

0	Thanks, It's solved now. answered Jan 22 at 17:27 rajendrasedhain 1●1

Answer 3

0

There is actually a 3rd choice in addition to what @Matt Whitfield suggested. Use the Group by clause:

select B.BookID, B.Title, a.authorName 
  from Book b 
  join book_author ba on b.bookID = ba.bookID 
  join author a on ba.authorID = a.ID
where a.ID = @authorID 
group by a.authorName, b.bookID, b.title
order by b.Title

answered Jan 22 at 18:18

Rob Allen
13●4

This actually won't help since the OP only wants one row per book not per book/author.

(Jan 22 at 18:46) TG

Answer 4

0	You need either a UDF that builds the CSV of authers for one book or this solution: http://ask.sqlteam.com/questions/1628/stored-procedure-if-else-condition answered Jan 22 at 18:47 TG 1513●1●5

Duplicate Records avoid while joining the tables

Follow this question

Related questions