Duplicate Records avoid while joining the tables

Question

question

rajendrasedhain asked Jan 22, '10

Duplicate Records avoid while joining the tables

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

sql-server-2008 sql-server-2005

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-01-22T14:00:55Z

Matt Whitfield answered Jan 22, '10

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-01-22T14:27:58Z

rajendrasedhain answered Jan 22, '10

Thanks, It's solved now.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-01-22T15:18:57Z

Rob Allen answered Jan 22, '10

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2010-01-22T15:47:50Z

TG answered Jan 22, '10

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question