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.

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 ASP programming group. and thanks for your replies!

 

[tlbroadbent]

There are a number of ways to accomplish this. One way is to use cursors. Someone will probably suggest a method using cursors. I suggest the following alternate method using table variables and a while loop in SQL 2000.

set nocount on

--Create two tables
Declare @tmp1 Table
(ContactID Int, RoleName varchar(40), RecNo Int)
Declare @tmp2 Table
(ContactID Int Primary Key, RoleList varchar(8000))

--Insert contacts and roles into 1st table
Insert @tmp1
Select ContactID, RoleName,
(Select count(*) From ContactRoles c2 Join Roles r2 On c2.RoleID=r2.RoleID
Where c2.ContactID=c.ContactID
And r2.RoleName<=r.RoleName)
From ContactRoles c Join Roles r
On c.RoleID=r.RoleID

--Insert Contacts and empty role list into 2nd table
Insert @tmp2
Select Distinct ContactID, ''
From Contacts

--Create and initialize working variables
Declare @rn int, @mn int
Select @rn=1, @mn=Max(RecNo) From @tmp1

--Loop through records, updating role list
While @rn<=@mn
Begin
Update @tmp2
Set RoleList=RoleList+Case When RoleList='' Then '' Else ', ' End+RoleName
From @tmp2
Join @tmp1
On @tmp2.ContactID=@tmp1.ContactID
Where @tmp1.RecNo=@rn
Set @rn=@rn+1
End

--Create final result set
Select
r.ContactID, c.ContactName,
c.ContactEmail, c.ContactPhone,
r.RoleList
From @tmp2 r
Join Contacts c
On r.ContactID=c.ContactID
Order By c.ContactName Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[Funka]

Looks Beautiful and elegant. I'll be plugging this in first thing Monday morning to try it out. Thank you!