How can I get one result from table 2 for each item in table 1 using CFQUERY?
I am trying to execute a query similar 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 need one result per unit for the TenantID, and I would like this to be the highest tenant ID. In other words, the Id should look like this:
101 590
102 97
103 44
I'm at a loss. Ive tried to create a Loop inside a Query but was not successful. I know this should be easy, but I can't figure it out. I would appreciate any suggestions
source to share
Group the query results in the database and use the 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 use a Query Query to do the same as soon as the database returns a full set of results in ColdFusion. Please note: The SQL below runs in memory, not 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 for the cfoutput tag that you can use. Internal cfoutput runs once per tenant. (I find this to be 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>
source to share