SQL builder SQL Statement

iccsi wrote: My question is the 300 queries can be simplified as a simple SQL like Select MyName, count(MyID)  from myTable where myName =  'Name1' group by MyName Select MyName, count(MyID) from myTable where myName = 'Name2' group by MyName Select MyName, count(MyID) from myTable where myName = 'Name3' group by MyName can be some one use like following Select MyName, count(MyID) from myTable group by MyName or save as a stored procedure which the web server or web page only hit database server once instead of 300 times. I would like to know does ColdFusion is smart enough to know the SQL are similar and only run SQL once against dabase server.

In my opinion, query-of-a-query is a smart enough solution to the problem. In the following example, ColdFusion visits the database just once!

<cfquery name="mainQuery" datasource="myDSN">

select MyName, count(MyID) from myTable

group by MyName

</cfquery>

<cfquery dbType="query" name="subQuery1">

select *

from myQuery

where myName =  'Name1'

</cfquery>

<cfquery dbType="query" name="subQuery2">

select *

from myQuery

where myName =  'Name2'

</cfquery>

...

...

<cfquery dbType="query" name="subQuery300">

select *

from myQuery

where myName =  'Name300'

</cfquery>