Formatting one to many results

[emozley]

Hi,

I have a database that has 2 tables - a list of documents and a list of authors. A document can have 0, 1 or more authors linked to it. I have a third table that keeps track of what authors are linked to what document.

I am trying to figure out the best way of producing an HTML table that has one row per document with two columns. In the first column I want the document title and in the 2nd column I want the authors. If there is more than one author I want them to appear in the same cell (i.e. on the same row).

I can write a query easily enough that produces a recordset that has the info I need but the code that keeps tack of whether to move onto the next row in the HTML table and whether to display the document name etc. is getting really untidy.

The other option is to load the documents into an array and then perform a SQL lookup for the authors for each row in the array but this would mean executing a lot of queries.

The other option is to load the documents and the authors into an array and then have a nested loop however I am wondering if this is inefficient as well - if I have 100 documents with 50 authors then this would be 5000 cycles to display 100 rows.

Are there any other options anyone can think of? Would be very grateful for any guidance.

Thanks very much

Ed Mozley

 

[gmmastros]

In this type of situation, I prefer to have the database do the "heavy lifting" for me. Specifically, I would write a query that would return a comma separated list of authors from the database. This would mean 1 row per document (smaller recordset) and simpler presentation code.

I am really only familiar with Microsoft SQL Server. If this is the database engine you are using, please show the existing query and also let me know the version of the database engine (SQL2000, 2005, 2008, 2008r2, 2012).



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[emozley]

Hi George,

This sounds excellent!

So my query so far is:

SELECT Docs.DocID, Docs.Title, Authors.FirstName, Authors.Surname
FROM (Docs
LEFT JOIN DocAuthors ON Docs.DocID=DocAuthors.DocID)
LEFT JOIN Authors ON DocAuthors.AuthorID=Authors.AuthorID
ORDER BY Docs.Title ASC

It is probably worth mentioning at this point that it would be helpful if a special character could be used to separate the Authors string rather than a comma as there are some authors who have commas in their name.

Thanks very much!

Ed

 

[emozley]

Hi George,

Thought you might be interested to know after a fair bit of digging around I found something that did what I wanted. Thank you for your advice as I didn't even the approach existed until your reply.

So the DB server does the grunt work and the authors are stuck together in a single string.

My tables are different as I wanted to understand the basic principle first and then apply it to my actual application.

Thanks very much!

USE CLIENTS

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID(N'[dbo].[fnPartiesToString]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION dbo.fnPartiesToString
GO

CREATE FUNCTION dbo.fnPartiesToString
(
    @DocID int
)
RETURNS varchar(Max)
AS
BEGIN
 
    DECLARE @result varchar(max)
 
    SELECT    @result = coalesce(@result + ', ', '') + Party
    FROM    ScheduleDocPartyLinks
    LEFT JOIN Parties ON ScheduleDocPartyLinks.PartyID=Parties.PartyID
    WHERE    ScheduleDocPartyLinks.DocID = @DocID
    
    RETURN @Result
END
GO
 
select schedules.scheduleid, schedulefolders.folderid, folders.foldername, folders.foldernotes, 
documents.duid, documents.folderid, documents.title, documents.description, 
documents.docdate, documents.DayUnknown, documents.MonthUnknown, scheduledocuments.visible,
dbo.fnPartiesToString(Documents.DocID) AllParties
from (((schedules 
left join schedulefolders on schedules.scheduleid=schedulefolders.scheduleid) 
left join folders on schedulefolders.folderid=folders.folderid) 
left join documents on folders.folderid=documents.folderid) 
left join scheduledocuments on documents.docid=scheduledocuments.docid
where schedules.SUID='TT061EO9Z94VCPN3KJQ6' 
order by schedulefolders.OrderOnSchedule ASC, scheduledocuments.orderonschedule asc

 

[gmmastros]

emozley,

Why are you left joining to the Parties table within your function? It seems to me that you should probably be using an inner join instead.

When you left join, it's possible to get a NULL value for all the columns in the right side table (in your case, Parties). In this situation, you wouldn't want to include it in the list of parties, right? I would suggest a couple small changes to the function.

CREATE FUNCTION dbo.fnPartiesToString
(
    @DocID int
)
RETURNS varchar(Max)
AS
BEGIN
 
    DECLARE @result varchar(max)
 
    SELECT  @result = coalesce(@result + ', ', '') + Party
    FROM    ScheduleDocPartyLinks
            [!]INNER[/!] JOIN Parties 
              ON ScheduleDocPartyLinks.PartyID=Parties.PartyID
    WHERE   ScheduleDocPartyLinks.DocID = @DocID
            [!]And Party > ''[/!]

    RETURN @Result
END

Eventhough this code does an inner join on PartyId, that's still no guarantee that Party will not contain a NULL. If it did, your results would not be correct. It's possible that your database prevents NULL in the Party column. It's also possible that Party could be an empty string. If there is an empty string, you would end up with "A,B,,D,E" which is also not what you want.

Whenever you use this technique, you should get in to the habit of including the extra filter to make sure you have good data that you are concatenating together.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[emozley]

Hi George,

Thanks for this - yes that certainly makes sense and gives me a much cleaner recordset to work with. I think the less processing I have to do in ASP the better.

So now just one final question... how would I turn the whole lot into a stored procedure?

I would want to replace the long SUID string with @SUID and then call it along the lines of

EXEC ViewSchedule('TT061EO9Z94VCPN3KJQ6')

I tried making a stored procedure but it told me the function already existed so I am doing something wrong!

E.

 

[gmmastros]

Your stored procedure should not be creating the function. When you create the function, it is stored in the database. You simply need to create a stored procedure that uses the function, like this:

Create Function dbo.ViewSchedule @SUID VarChar([!]20[/!])
AS
SET NOCOUNT ON

select schedules.scheduleid, 
       schedulefolders.folderid, 
       folders.foldername, 
       folders.foldernotes, 
       documents.duid, 
       documents.folderid, 
       documents.title, 
       documents.description, 
       documents.docdate, 
       documents.DayUnknown, 
       documents.MonthUnknown, 
       scheduledocuments.visible,
       dbo.fnPartiesToString(Documents.DocID) AllParties
from   (((schedules 
       left join schedulefolders 
         on schedules.scheduleid=schedulefolders.scheduleid) 
       left join folders 
         on schedulefolders.folderid=folders.folderid) 
       left join documents 
         on folders.folderid=documents.folderid) 
       left join scheduledocuments 
         on documents.docid=scheduledocuments.docid
where  schedules.SUID=@SUID
order by schedulefolders.OrderOnSchedule ASC,        
       scheduledocuments.orderonschedule asc

Note the part in RED. Instead of 20, you should find out what the defined column width is for schedules.SUID, and use that value instead.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[emozley]

I see thank you!

After your reply I did a bit of digging around and found the function I created under "Scalar-valued functions".

It's all making sense now...

Thanks again

Ed