Help with DB Development

[riechan]

Hi guys. I'm sort of having some problem with the way our database's structure is supposed to be like. This concerns books and authors.
Taking the following notes into consideration:

- There can be 1 or more authors of a book
- There can be 1 or more books having the same author

Okay, taking those two things into consideration, this would be the table structure (please don't mind the simpleness of the tables, as they are only used for examples):

[Author]
AuthorID (PK)
AuthorName

[AuthorDetails]
AuthorID, BookID (PK)

[Book]
BookID (PK)
BookTitle

Given this data:
Book Title: MyTitle
Authors: Author1, Author2, Author3

What query should I use to make the query result look like this:
Title AuthorName1 AuthorName2 AuthorName3
MyTitle Author1 Author2 Author3

Because, if I query this statement:

SELECT     Book.BookID AS Expr1, Book.CallNumber AS Expr2, Book.Title AS Expr3, AuthorDetails.BookID, AuthorDetails.AuthorID, Author.AuthorID AS Expr4
FROM         Book INNER JOIN
                      AuthorDetails ON Book.BookID = AuthorDetails.BookID INNER JOIN
                      Author ON AuthorDetails.AuthorID = Author.AuthorID
GROUP BY Book.BookID, Book.CallNumber, Book.Title, AuthorDetails.BookID, AuthorDetails.AuthorID, Author.AuthorID

It results into the authors being broken down into three separate records, ie:

MyTitle Author1
MyTitle Author2
MyTitle Author3

Anyone?

[GaryMazzone]

Are you using SQL Server?

[riechan]

Yes.

[GaryMazzone]

Look at declaring a variable of type varchar and use that with and ISNULL and appending the field.


something like this:

Declare @AuthorNames NVARCHAR(MAX)
select Book.Title ,@AuthorNames = IsNull(@AuthorNames  + ', ','') + [AuthorName] 
FROM         Book INNER JOIN
                      AuthorDetails ON Book.BookID = AuthorDetails.BookID INNER JOIN
                      Author ON AuthorDetails.AuthorID = Author.AuthorID

[leinad31]

Structure is correct riechan. You are confusing output/formatting/reporting requirements with database/storage design requirements.

Simply create a T-SQL function that returns all authors (regardless of number rather than hardcoded joins as will happen with SQL based solution) for a given book. The function will query based on bookID and iterate through relevant records, concatenating the author names, finally returning a csv per bookID.

[riechan]

@leinad31: I'm thinking that this is like the example that Mazz1 cited. And that it needs to be done on the front-end as well, not just on the back-end, am I right? I'm going to test the code that Mazz1 provided. I hope that it works.

Edited:

I edited and tested Mazz1's example in SQL 2000, but I got this error:

DECLARE @AuthorNames NVARCHAR(1000)
SELECT Book.Title, @AuthorNames = IsNull(@AuthorNames  + ', ','') + [AuthorName] FROM Book 
	INNER JOIN AuthorDetails ON Book.BookID = AuthorDetails.BookID 
	INNER JOIN Author ON AuthorDetails.AuthorID = Author.AuthorID

Server: Msg 141, Level 15, State 1, Line 4
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

[riechan]

@Mazz: Thank you very much! I got the code up and running! The only problem now is to get it the AuthorIDs of the Author's in the @AuthorNames. Any ideas?

By the way, here's the code that I used, based off on Mazz's example, with a few tweaks:

DECLARE @AuthorNames NVARCHAR(1000)
SELECT @AuthorNames = IsNull(@AuthorNames  + ', ','') + [AuthorName] FROM Book 
	INNER JOIN AuthorDetails ON Book.BookID = AuthorDetails.BookID 
	INNER JOIN Author ON AuthorDetails.AuthorID = Author.AuthorID 

SELECT @AuthorNames, Book.BookID, Book.Title FROM Book
	INNER JOIN AuthorDetails ON Book.BookID = AuthorDetails.BookID 
	INNER JOIN Author ON AuthorDetails.AuthorID = Author.AuthorID 
        GROUP BY Book.BookID, Book.Title

I'm also wondering if I can place the whole code into a single SQL Data Adapter in VB...

[GaryMazzone]

Do the same, Create a NVARCHAR variable, set it equal to the AuthorID same name. Just Convert or cast to NVARCHAR

[riechan]

Question. Why is it that this code:

SELECT @AuthorNames = IsNull(@AuthorNames  + ', ','') + [AuthorName] FROM Book
INNER JOIN AuthorDetails ON Book.BookID = AuthorDetails.BookID
INNER JOIN Author ON AuthorDetails.AuthorID = Author.AuthorID

returns all of the records found in the Author table? Why is it not that when we execute this code:

DECLARE @AuthorNames NVARCHAR(1000)

SELECT @AuthorNames = IsNull(@AuthorNames  + ', ','') + [AuthorName] FROM Book
INNER JOIN AuthorDetails ON Book.BookID = AuthorDetails.BookID
INNER JOIN Author ON AuthorDetails.AuthorID = Author.AuthorID


SELECT Book.BookID, Book.CallNumber, Book.Title, @AuthorNames AS 'Authors' FROM Book
INNER JOIN AuthorDetails ON Book.BookID = AuthorDetails.BookID
INNER JOIN Author ON AuthorDetails.AuthorID = Author.AuthorID
GROUP BY Book.BookID, Book.CallNumber, Book.Title

It would only return all of the Authors that are related to that book in the AuthorDetails table?

[GaryMazzone]

The two statements should not be used together.

The first gets a list of all auhors that have a book (any) book listed.

You need to combine the part concatinating the names into the second select if you want it by book

[riechan]

Thing is, it says here, that SELECT statements that assigns values cannot be combined with data retrieval SELECT statements. Any ideas?