There are times when you do not want to use a query of queries. I try to avoid using them at all costs. They are slow and can use far too many system resources. As an alternative you can use Java indexOf, especially if you only want to determine if a value exists in a query. For example let's use the Northwind data source:

<cfquery name="GetCustomer" datasource="Northwind">
   SELECT   OrderID, CustomerID, EmployeeID
   FROM   Orders
</cfquery>

This quarry will pull over 800 results. To avoid re-querying the database to determine if a returned value exists and to avoid a query of queries, test for the index of that value in the query.

<cfset SearchCustomerID="RATTC">

<cfset TestIndex=GetOrder["CustomerID"].IndexOf(JavaCast("string", SearchCustomerID))>

<cfif TestIndex GTE 0>
   <cfoutput>#SearchCustomerID# = #TestIndex#</cfoutput><br />
</cfif>

Remember though this is java and the index starts at zero unlike cold fusion which starts at one. This means that the index returned is 14 but the actual row number from the result is 15. If the index returned is a negative number it does not exist in the query. Therefore zero or any positive number means the value exists.

If you find this post useful please leave a comment and let me know how you used the information.