Queries with multiple rows per record

Hope the title makes sense.

On our sites, we have a number of queries that return two or three rows per record - books, in this case. A record is a book, and the multiple rows are returned because a book can have multiple authors. The join with the authors table results in multiple rows.

This also results in multiple books being shown in overviews like recently published books, and this has been bothering us for a while.

The main question is: Is there a neat way to catch this in the presentation layer? (both JSP(F) as XSL). We've fixed this in the 'book detail' query, but that's relatively easy because it's one book. It becomes a different story when there's multiple books, because then it becomes more difficult to separate two records from each other.

Does anyone else have experience with this problem, and found a proper solution?

Yes, I already know that this should be handled in the layer between the DB and the view, but we have no such layer at this time. We use straight query elements whose results are sent to a JSP template. In a distant future we'll develop a middleware layer, but that's a future far, far away, it seems. In the meantime, we'll need a solution for books being displayed multiple times when they have multiple authors.

Any ideas?

Hi,

You could try to create a stored procedure that flattens the results as described on this page.

Cheers.

CREATE PROCEDURE [dbo].[bookauthors] @id int, @authors varchar(1000) OUTPUT
	
AS
BEGIN
	SET NOCOUNT OFF;

	set @authors = ''

	select 
		@authors = @authors + cast(j.author as varchar(10)) + ', ' 
	from
		bookauthor j 

		inner join book o2 		
		on o2.id = j.book
	where 
		j.book = @id
	
	return
END