There are 3 things I am seeing with this query that would help you...
1. Since you are doing a X.ID = Y.ID you are essentially doing a join,
though the more up-to-date way would be to specify INNER JOIN
2. The use of a * is not such a great practice. It is always recommended to
layout everything you need and only what you need, even if you are pulling
back all rows lay those out here.
3. You really should use a cfqueryparam, no and if's or buts about it :-).
If you go read on CF-TALK about the SQL injections it will give you the
reason why. Essentially this is a security measure that also has some
performance benefits as well.
Happy CF'ing!
J.J. Merrick
On 6/13/07, Brian Petti <[EMAIL PROTECTED]> wrote:
>
> Hi All,
> Is the code snippet below the correct way to get information from 2
> tables? Should I specify a join in the SQL? This seems to work with
> out specifying it however.
>
> Thanks for any suggestions.
>
> -Brian
>
>
> <cfcomponent>
> <cffunction name="ListBusinesses" returntype="query">
> <cfargument name="CatID" required="false" type="numeric"
> default="0">
> <cfargument name="bizname" required="false" type="string">
> <cfargument name="CountyID" required="false" type="numeric">
> <cfargument name="City" required="false" type="string">
> <cfargument name="stateid" required="false" type="numeric">
> <cfargument name="zip" required="false" type="string">
> <cfargument name="bizid" required="false" type="numeric">
>
> <cfquery name="getBusinesses"
> datasource="kenhugh_america411">
> SELECT
> *
> FROM
> tblBiz,tblLocation
> WHERE
> tblBiz.Countyid = tblLocation.Countyid
> AND
> CatID = #Val(Arguments.CatID)#
> <cfif isdefined('arguments.bizname')>
> AND bizname LIKE '%#arguments.bizname#%'
> </cfif>
> <cfif arguments.CountyID GT 0 >
> AND CountyID = #val(arguments.CountyID)#
> </cfif>
> <cfif isdefined('arguments.City') >
> AND BizCity LIKE '%#arguments.City#%'
> </cfif>
> <cfif arguments.stateid GT 0 >
> AND stateid = #val(arguments.stateid)#
> </cfif>
> <cfif isdefined('arguments.zip')>
> AND bizzip LIKE '%#arguments.zip#%'
> </cfif>
> <cfif arguments.bizid GT 0 >
> AND bizid = '#arguments.bizid#'
> </cfif>
> </cfquery>
>
> <cfreturn getBusinesses>
> </cffunction>
> </cfcomponent>
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
The most significant release in over 10 years. Upgrade & see new features.
http://www.adobe.com/products/coldfusion?sdid=RVJR
Archive:
http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:2839
Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
