October 2, 2006 at 4:33 am
Hi
My limited SQL is struggling with this problem so any suggestions would be greatly appreciated!!
I have three tables:- Book, BookAuthor and Person. BookAuthor links Book and Person by holding the ID of a person and the ID of the book they have written. One book can have many authors.
I am trying to write a query to give me the following results:
Book1 (and some of its details) Author1, Author2, Author3
But what I'm getting is:
Book1 (and details) Author1
Book1 (and details) Author2
Book1 (and details) Author3
My query looks like this:
SELECT dbo.BookAuthor.BookID AS Publication, dbo.BookAuthor.Sequence,
dbo.Person.Surname + ' ' + dbo.Person.Initials + ',' AS Authorlist, dbo.Book.Title, dbo.Book.ISBN, dbo.Book.YearOfPublication, dbo.Publisher.PublisherName, dbo.Book.PlaceOfPublication, dbo.Book.Verified, dbo.Book.BookType
FROM dbo.Person
RIGHT OUTER JOIN dbo.Book
INNER JOIN dbo.BookAuthor ON dbo.Book.BookID = dbo.BookAuthor.BookID ON dbo.Person.PersonID = dbo.BookAuthor.PersonID
RIGHT OUTER JOIN dbo.Publisher ON dbo.Book.PublisherID = dbo.Publisher.ID
WHERE (dbo.BookAuthor.BookID IN
(SELECT BookAuthor.BookID
FROM Person
RIGHT JOIN BookAuthor ON Person.PersonID = BookAuthor.PersonID))
October 2, 2006 at 5:59 am
Assuming:
1. A book has at least one author
2. dbo.BookAuthor.[Sequence] goes from 1 for first author to 3 for third author
Try something like:
SELECT B.BookID AS Publication
,B.Title
,B.ISBN
,B.YearOfPublication
,P.PublisherName
,B.PlaceOfPublication
,B.Verified
,B.BookType
,P1.Surname + ' ' + P1.Initials
+ ISNULL(', ' + P2.Surname + ' ' + P2.Initials, '')
+ ISNULL(', ' + P3.Surname + ' ' + P3.Initials, '') AS Authorlist
FROM dbo.Book B
JOIN dbo.Publisher P ON B.PublisherID = P.[ID]
JOIN dbo.BookAuthor X1 ON B.BookID = X1.BookID
JOIN dbo.Person P1 ON X1.PersonID = P2.PersonID
AND X1.[Sequence] = 1
LEFT JOIN (dbo.BookAuthor X2
JOIN dbo.Person P2 ON X2.PersonID = P2.PersonID
AND X2.[Sequence] = 2) ON B.BookID = X2.BookID
LEFT JOIN (dbo.BookAuthor X3
JOIN dbo.Person P3 ON X3.PersonID = P3.PersonID
AND X3.[Sequence] = 3) ON B.BookID = X3.BookID
ps Try to avoid using reserved names like sequence and id as column names.
October 2, 2006 at 7:21 am
Also your first query is fine. The application should be the one doing the work to display the data correctly (especially in pivot queries like this one). There are however a few cases where no application exists to do the work and you need to use the self left joins. But those are pretty rare in my experience.
October 3, 2006 at 7:35 am
Thanks for your replies, I tried Ken's query in the Query analyzer and got the following error msg: "The column prefix 'P2' does not match with a table name or alias name used in the query". It doesn't seem to like having more than one instance of the table.
I've been reading up on pivot queries but all the examples use numeric data that is included in aggregate functions like SUM etc. As I understand it, I can't use such functions on string data. Can anyone point me to a good example that uses string data?
October 3, 2006 at 8:59 am
An obvious typo on my part which is now corrected:
SELECT B.BookID AS Publication
,B.Title
,B.ISBN
,B.YearOfPublication
,P.PublisherName
,B.PlaceOfPublication
,B.Verified
,B.BookType
,P1.Surname + ' ' + P1.Initials
+ ISNULL(', ' + P2.Surname + ' ' + P2.Initials, '')
+ ISNULL(', ' + P3.Surname + ' ' + P3.Initials, '') AS Authorlist
FROM dbo.Book B
JOIN dbo.Publisher P ON B.PublisherID = P.[ID]
JOIN dbo.BookAuthor X1 ON B.BookID = X1.BookID
JOIN dbo.Person P1 ON X1.PersonID = P1.PersonID
AND X1.[Sequence] = 1
LEFT JOIN (dbo.BookAuthor X2
JOIN dbo.Person P2 ON X2.PersonID = P2.PersonID
AND X2.[Sequence] = 2) ON B.BookID = X2.BookID
LEFT JOIN (dbo.BookAuthor X3
JOIN dbo.Person P3 ON X3.PersonID = P3.PersonID
AND X3.[Sequence] = 3) ON B.BookID = X3.BookID
As you are only producing a concatenated string, a function will also work but, due to the overhead of calling it, will probably be less efficient in this case.
Something LIKE this should work:
CREATE FUNCTION dbo.GetAuthors
(
@BookID INT
)
RETURNS VARCHAR(8000)
BEGIN
DECLARE @Result VARCHAR(8000)
SET @Result = ''
SELECT @Result = @Result + ISNULL(', ' + P.Surname + ' ' + P.Initials, '')
FROM dbo.BookAuthor X
JOIN dbo.Person P ON X.PersonID = P.PersonID
WHERE X.BookID = @BookID
ORDER BY X.[Sequence]
RETURN SUBSTRING(@Result, 3, 8000)
END
GO
SELECT B.BookID AS Publication
,B.Title
,B.ISBN
,B.YearOfPublication
,P.PublisherName
,B.PlaceOfPublication
,B.Verified
,B.BookType
,dbo.GetAuthors(B.BookID) AS Authorlist
FROM dbo.Book B
JOIN dbo.Publisher P ON B.PublisherID = P.[ID]
ps This has NOT been tested as NO DDL and test data were provided.
October 3, 2006 at 9:55 am
Thanks that's helpful. I'm not sure that I can use it in my application though because I do not always know how many authors there are for each book, so the results will vary depending on the number of authors.
Some posters on other forums have advised that I do not try to format the data within my query but to do it within my front end application so I'm going to try that route.
Thanks for your help.
October 3, 2006 at 10:48 am
Other forum or this forum??
October 4, 2006 at 1:27 am
Here's the link.....
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply