Skip to main content
June 19, 2011
Question

dialing down through successive fields based on count

  • June 19, 2011
  • 1 reply
  • 935 views

i have a table with multiple fields that i want to query until i have less than a recordcount of 20, how should i write the query

table: mystuff

fields: fld1, fld2, fld3, fld4 etc

final recordcount: less than 20

i need to query all of successive field. no MAX as would limit the scope of the query

<cfquery name="myqry" datasource="myds">
    SELECT *

    FROM mystuff   

    WHERE fld1 = 'cats'  

          <cfif records GT 20>

               and fld2 = 'siamese'

                    <cfif records GT 20>

                         and fld3 = 'white'

and keep dialing through fields until recordcount is less than 20. i do not want to limit the records of each successive field but to get all for the next step down.         

</cfquery>

tnx in advance

This topic has been closed for replies.

1 reply

Inspiring
June 20, 2011

Go to the db and get all your cats.  Use Q of Q for the rest of it.

June 20, 2011

hi dan. tnx your interest again...

what?

Owainnorth
Inspiring
June 20, 2011

How do you expect ColdFusion to know how many records are retrieved before it's even sent the query to the database engine?

As Dan says you'll have to bring your categories back into CF and do your looping/filtering logic in there, possibly using Query Of Queries.