Skip to main content
A
Anonymous
May 3, 2009
Question

querying sucessive fields in where statement until variable found

  • May 3, 2009
  • 1 reply
  • 981 views

i have a table that i want to query first one field and if no results, query next field and so on...

i am doing it now by doing sucessive seperate queries whic works but is probably an inefficient way of doing so... can this be written as a single query??? note that i am using the same variable on each query and only changing the field name i.e. blm_ttl, blm_txt, blm_by

<cfquery name="ckstry" datasource="asker">
  SELECT MAX(blmID) AS blmrID
  FROM blme
  WHERE blm_ttl LIKE ('%#blm_adurl1#%')
</cfquery>


<cfif ckstry.blmrID IS "">


  <cfquery name="ckstry" datasource="asker">
    SELECT MAX(blmID) AS blmrID
    FROM blme
    WHERE blm_txt LIKE ('%#blm_adurl1#%')
  </cfquery>


</cfif>

<cfif ckstry.blmrID IS "">


  <cfquery name="ckstry" datasource="asker">
    SELECT MAX(blmID) AS blmrID
    FROM blme
    WHERE blm_by LIKE ('%#blm_adurl1#%')
  </cfquery>


</cfif>

This topic has been closed for replies.

1 reply

D
Dan_Bracuk
Inspiring
May 3, 2009

query db

select 1 x, max(blm_id) id

from blm

when blm_ttl like '%#blm_adurl1#%'

group by x

union

select 2 x max(blm_id) id

from blm

when blm_txt like '%#blm_adurl1#%'

group by x

etc

Q of Q 1

select min(x)

from the first query

Q of Q 2

select id

from the first query

where x = the value from the first Q of Q.

A
Anonymous
May 3, 2009

I only want to query successive fields if the preceeding field did not contain the variable. as even none found results in a recordcount, i cannot use that as a condition. only that blmrID = "" or ?

Seems should be an IF statement inside query but cannot figure ou correct way to word it as in

<cfquery name="ckstry" datasource="asker">
   SELECT MAX(blmID) AS blmrID
   FROM blme
   WHERE

          blm_ttl LIKE ('%#blm_adurl1#%')

     if (no variable found in blm_ttl)

     begin

          blm_txt LIKE ('%#blm_adurl1#%')

     end

     if (no variable found in blm_txt)

     begin

          blm_by LIKE ('%#blm_adurl1#%')

     end

    
</cfquery>

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices