Basic cfloop question

[nycchris]

I have three tables: People, PhoneNumbers, and EmailAddresses. They are tied together with a field called PersonID. I want to have a list of names with the phone numbers and email addresses listed below. Example:

John Doe
(212) 222-2222
(917) 111-1111
john@johndoe.com

Jane Doe
(241) 555-5555
jane@janedoe.com

Right now, I'm using:

<cfquery name="People" datasource="MyDatasource">
SELECT People.FirstName, People.LastName
FROM People
</cfquery>

<cfquery name="PhoneNumbers" datasource="MyDatasource">
SELECT PhoneNumbers.PhoneNumber
FROM PhoneNumbers
WHERE PhoneNumbers.PersonID = #qPeople.PersonID#
</cfquery>

<cfquery name="EmailAddresses" datasource="MyDatasource">
SELECT EmailAddresses.EmailAddress
FROM EmailAddresses
WHERE EmailAddresses.PersonID = #qPeople.PersonID#
</cfquery>


In the body I have:

<table border="0" cellpadding="5">

<cfoutput query="qPeople">
<tr>
<td>

#qPeople.FirstName# #qPeople.LastName#<BR>

<cfloop query="qPhoneNumbers">#qPhoneNumbers.PhoneNumber#<BR></cfloop>

<cfloop query="qEmailAddresses">#qEmailAddresses.EmailAddress#<BR></cfloop> 

</td>                   
</tr>
</cfoutput>
</table>


I'm doing something wrong because the loop only returns the records of the first person in the People query. Thanks in advance for any assistance.

[Michael Borbor · Accepted solution]

The problem is that the queries are misplaced, you should place the two

queries (qPhoneNumbers,qEmailAddresses) inside the cfoutput

[Dan_Bracuk]

Step 1 - Consolidate your 3 queries into 1, and order by the person id.

Step 2 - Read the cfoutput section of the cfml reference manual.  Pay attention to the group attribute.

[TLC-IT]

Also consider using a single query which uses a LEFT OUTER JOIN construct.

Think about it.  What you're really trying to output here is one "set of records," and yet it just-so-happens that the information for those records comes from three different tables:  people, phone-numbers, and e-mail addresses.  You want "people" in any case, and you want "phone numbers" and/or "email addresses" to appear if they exist.  A perfect application for a LEFT OUTER JOIN.

Well, maybe.  If a person with two phone numbers has three e-mail addresses then you will have six rows in your output:  one for every combination.  This may or may not be what you want.

Fiddle with your queries (outside of ColdFusion) to try to come up with what you're looking for, ideally in just one query.