CODEWITHSUNDEEP
coldfusioncfquery
Will Kelty
Will Kelty

Reputation: 41

How can I get one result from Table2 for each item in Table1 using CFQUERY?

I’m trying to do a query simliar to:

 <CFQUERY Name=GetResults Database=Rent> SELECT * FROM Units, Tenants
  WHERE Units.UnitID=Tenants.UnitID</CFQUERY>
 <CFOUTPUT Query=GetRetults>
 #UnitName# #TenantID#<BR>
 </CFOUTPUT>

This is what the results look like

101 57
101 199
101 204
101 590
102 85
102 97
103 44

I only want one result for each unit for the TenantID, and I would like it to be the highest Tenant ID. In other words I’d like the results to look like:

101 590
102 97
103 44

I’m at a loss. I’ve tried creating a Loop within the Query, but have not been successful. I know this must be simple but I can’t figure it out. I'd appreciate any suggestions

Upvotes: 3

Views: 107

Answers (1)

Brad Wood
Brad Wood

Reputation: 3953

Group your query results on the database and use an aggregate function. Something like this should work:

<CFQUERY Name="GetResults" Database="Rent"> 
  SELECT u.unitID, u.unitName, max( t.tenantID ) as maxTenantID
  FROM Units u
  INNER JOIN Tenants t ON u.UnitID = t.UnitID
  GROUP BY u.unitID, u.unitName
</CFQUERY>

If changing the SQL is not an option, you can employ a query of queries to do the same thing once the database returns the full result set to ColdFusion. Note, the SQL below runs in memory and not on the database.

<CFQUERY Name="groupedResults" dbtype="query"> 
  SELECT unitID, unitName, max( tenantID ) as maxTenantID
  FROM GetResults
  GROUP BY unitID, unitName
</CFQUERY>

And finally, there is a group attribute to the cfoutput tag as well that you can use. The inner cfoutput is run once for each tenant. (I consider this the ugliest approach)

<CFOUTPUT Query="GetRetults" group="unitID">
  #UnitName# 
  <!--- Reset every time --->
  <cfset maxTenantID = 0>    
  <cfoutput>
    <cfset maxTenantID = max( maxTenantID, TenantID )>
  </cfoutput>
  #maxTenantID#<BR>
</CFOUTPUT>

Upvotes: 5

Related Questions