Re: Help with DB Development
Are you using SQL Server?
Re: Help with DB Development
Re: Help with DB Development
Look at declaring a variable of type varchar and use that with and ISNULL and appending the field.
something like this:
sql Code:
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
Re: Help with DB Development
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.
Re: Help with DB Development
@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:
Code:
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
Code:
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.
Re: Help with DB Development
@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:
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 @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...
Re: Help with DB Development
Do the same, Create a NVARCHAR variable, set it equal to the AuthorID same name. Just Convert or cast to NVARCHAR
Re: Help with DB Development
Question. Why is it that this code:
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:
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?
Re: Help with DB Development
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
Re: Help with DB Development
Thing is, it says here, that SELECT statements that assigns values cannot be combined with data retrieval SELECT statements. Any ideas?