INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Formatting one to many results

Formatting one to many results

(OP)
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

RE: Formatting one to many results

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

RE: Formatting one to many results

(OP)
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

RE: Formatting one to many results

(OP)
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!

CODE

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 

RE: Formatting one to many results

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.

CODE

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

RE: Formatting one to many results

(OP)
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.

RE: Formatting one to many results

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:

CODE

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

RE: Formatting one to many results

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close