<cfoutput query="readesi" group="clientname">
#clientname#
<cfoutput>Anything you would to output from the sub-rows (occupants)
goes here </cfoutput>
</cfoutput>
You can even nest this, say if one occupant has multiple phone numbers. You
just have to keep saying "group=...." in your cfoutput tags.
----- Original Message -----
From: "hammerin hankster" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Friday, September 10, 2004 12:49 PM
Subject: Displaying One-to-Many Records
> I've got a database with about 12 tables and there are numerous
one-to-many and one-to-one relationships. For example, one table has client
information (name, address, etc) and a related table has occupant
information (a client can have multiple occupants/employees).
>
>
> I have all my coldfusion code written to insert/modify each table as a
standalone table and I can also query each table and display all the
records.
>
> Now I'm buiding a query to pull each client record and it's associated
occupant information (which also works). Unfortunately, I get a long
listing of multiple client/occupant rows.
>
> What I'd really like to do is have the query output just the client
information with a hyperlink to the related occupant data. This is where
I'm stuck. Below is my query:
>
>
> <CFQUERY NAME="readesi" DATASOURCE="esidb1">
> SELECT tblmasterclients.clientname, aliasname, alphaprefix, siteid,
infinity, authcode, pobox, poboxcombo, tblmasterclients.comments, did,
secdid, faxdid, faxext, eighthundreddid, svctype,
tblmasterclients.acctstatus, prepareddate, url, terminationreason,
terminationdesc, inactivedate, CommencementAnni, tblmasterclients.clientid,
occupantid, tbloccupants.clientname, occname, occtype, ext,
tbloccupants.acctstatus, suiteid, dddid, occcell, occemail, occphserial,
occemail, occremoteoffice, occhome, occpager, occvmbox, accesscardno,
cardassigned, cardreturned, nokeys, furnitureno, dateentered,
tbloccupants.comments, occip, tblmasterclients.clientid
>
> FROM tblMasterClients, tbloccupants
> where tblmasterclients.clientname = tbloccupants.clientname
>
> ORDER BY tblmasterclients.clientname
> </CFQUERY>
>
> And here are my links to modify client and occupant data:
>
> <td class="tdtext" align=center><a class="class2"
href=""> #</a>
> <br><a class="class2"
href=""> > </td>
>
> Any ideas?
>
> Thanks for taking the time!
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
