And and or in query?

[Guest]

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...

[JR__Bob__Dobbs]

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.