Skip to main content
A
Anonymous
March 11, 2011
Question

And and or in query?

  • March 11, 2011
  • 1 reply
  • 367 views

I have a database table that has "designs" in it. Each design belongs to a category 1-4. Each design also has an activity 0-99. At times I want to get the designs in activity 1 and activity 0. But I want to only get the designs from category 1. My query below is returning activities from all categories.

<cffunction name="getDesignsByAct" access="remote" returntype="query" hint="gets activities by id">
<cfargument name="send_id" type="numeric" required="yes" />
    <cfquery name="getDesignsByActQuery" datasource="#dsn#">
    SELECT *
    FROM designs_table
       
    WHERE design_activity = #send_id# // sent activity
    OR design_activity = 0 // general activity
    AND design_type_id = 1 // category
   
    ORDER BY design_activity;
    </cfquery>
<cfreturn getDesignsByActQuery>
</cffunction>

Thanks...

    This topic has been closed for replies.

    1 reply

    J
    JR__Bob__Dobbs
    Inspiring
    March 11, 2011

    I suspect that you need to add parenthesis in your WHERE clause to make explicit to which parts the OR applies.

    Try something like.

    SELECT *
    FROM designs_table
    WHERE ( design_activity = #send_id# OR design_activity = 0 )
        AND design_type_id = 1
    ORDER BY design_activity;

    You might also considering adding CFQUERYPARAM to your queries for increased performance and security.

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