Help with SQL

Thanks Dan!

use the group attribute like this:

<cfouput query = "aquery" group="something">
#grouped data#
<cfoutput>
#ungrouped data#
</cfoutput>
<cfoutput>

Also, make sure your have your query sorted by the field you intend to group.

THANKSt! It works great! I have one last question about outputting the information. I would like to group the data so that data from the "maintable" appears only once and the data from the other two tables to appear under it:
e.g.
<cfoutput name="getinfo">
id:1 John Doe (from main table)
Bob Smith (from table 2), Joe Blow (from table 2)...
NASA (from table 3, CIA from table 3,)....

id:2 Jane Smith (from main table)....
Julie Andrews (from table2).....
Hollywood (from table3)....
....
</cfoutput>

This is what I have :
<cfoutput query="getInfo" group by "id">
<strong>#id#- #fname_pi# #lname_pi# </strong>#fname_coi# #lname_coi# #lab_affil_level#, #lab_name#<br>
</cfoutput>
If I remove the group by "id", then I get a repition of data from the main table.

Your code does not show where #id_pi# comes from, but here is the basic
idea of a join. Which is more efficient then your looping solution at
least 99.725% of the time.

SELECT
aField,
bField,
cField

FROM
aTable INNER JOIN
bTable ON aTable.key = bTable.key INNER JOIN
cTable ON ...

A partial example from your code.

SELECT
maintable.id,
maintable.fname,
maintable.lname
table2.id,
table2.co_id,
table2.fname_2,
table2.lname_2

FROM
maintable INNER JOIN
table2 ON maintable.id = table2.id