Retrieving related records with a Minimum of Database Activity

[Funka]

hello! i am adding in contact management features to an application. Each Contact can have multiple roles in her origanization. there are two tables involved. (a third table is a lookup/results table, but that is not important to show).

CREATE TABLE Contacts (
ContactID int ,
ContactName varchar(200) ,
ContactEmail varchar(200) ,
ContactPhone varchar(200)
)

CREATE TABLE ContactRoles (
ContactRoleID int , -- just an arbitrary auto-number
ContactID int , -- related to table Contacts
RoleID int -- from the lookup table
)

so as you see, if one Contact serves three roles in the organization (e.g., Advertising, Marketing, and Sales) then there will be one row in the Contacts table to store her name, etc., and three related rows in the ContactRoles table to associate each of these roles with her ContactID. pretty simple so far.

so when it comes time to display all of the Contacts in the organization, i would like to build an HTML table with one row for each Contact, and have the last cell in the show all of her roles, as a comma-delimited list or whatver. but i want one row per contact, not multiple rows.

i can do this of course by generating multiple, nested recordsets: i.e., populate the first recordset with all Contacts, then loop through it one row at a time, generating another recordset of Roles for this contact, and hence build the table one row at a time. i do not like this approach as it means if my table is going to list 100 Contacts, it would require 101 recordsets. I would like to do this with one recordset, but am not sure how. I would like to avoid using Cursors if possible. So after much ado, my question is: how can i retrieve the data needed for this task in the most elegant, efficient way possible? my platform is ASP/ADO using SQLServer 2000.

please forgive the cross-post to the related SQL Server group. and thanks for your replies!

 

[adam0101]

This is what I usually do.

<table>
<%
sql=&quot;SELECT c.ContactName, c.ContactID, c.ContactEmail, c.ContactPhone, r.role FROM Contacts c INNER JOIN ContactRoles cr ON c.ContactID = cr.ContactID INNER JOIN Roles ON cr.RoleID = r.RoleID ORdER BY c.ContactName, c.ContactID&quot;
Set objRS=objConn.Execute(sql)

Do While Not objRS.EOF
  If lastContact<>objRS.Fields(&quot;ContactID&quot;) Then
    Response.Write &quot;</td></tr><tr>&quot;
    Response.Write &quot;<td>&quot; & objRS.Fields(&quot;ContactName&quot;) & &quot;</td>&quot;
    Response.Write &quot;<td>&quot; & objRS.Fields(&quot;ContactEmail&quot;) & &quot;</td>&quot;
    Response.Write &quot;<td>&quot; & objRS.Fields(&quot;ContactPhone&quot;) & &quot;</td>&quot;
    Response.Write &quot;<td>&quot;
  End If
  Response.Write objRS.Fields(&quot;ContactPhone&quot;) & &quot;<br>&quot;
  lastContact=objRS.Fields(&quot;ContactID&quot;) 
  objRS.MoveNext
Loop
%>
    </td>
  </tr>
</table>

 

[adam0101]

I messed up a couple things in my previous post.

"INNER JOIN Roles ON" should be "INNER JOIN Roles r ON"

Response.Write objRS.Fields(&quot;ContactPhone&quot & &quot;<br>&quot; should be Response.Write objRS.Fields(&quot;Role&quot & &quot;<br>&quot;

Basically what I'm doing is keeping track of the last contact ID as I'm looping through the records. Whenever the contact ID changes, you know it's a new contact and that the contact's information and role needs to be displayed. If the contactID is the same, it skips the contact information and just puts the next &quot;Role&quot;.

 

[Funka]

Thank you! i have a stored procedures that uses that exact syntax (well, almost identical, but i have more fields in the real app, of course), and while i have been narrowly focussed on molding the data into my needed format so that i could use my custom HTML grid object to display the results, it had escaped my thinking that sometimes building these things like this (by hand, the old-fashioned way) is quite flexible, and often times the best and fastest way to do this.

thanks for your quick response!