Mike, Are you making sure that either product_id or cat_id is required? If not then this query has the potential to select all the records from your DB. You also need to specify actual columns rather than the asterisk.
As for complexity - I've seen far worse :) I would use aliases instead of full table qualifiers (saves typing). Finally you are allowing arguments.order_key through the function. This will be (I assume) a string like "cat_id DESC" .... My caution would be that you make sure this string is properly validated at the server to insure it is indeed a column. If this argument comes from user input (like a drop down box) then it beomes an excellent candidate for SQL injection. Lot's of folks forget about "order by" clauses... It's pretty common to use user input to allow a user to order a column in this way. -Mark Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -----Original Message----- From: Mike Little [mailto:[EMAIL PROTECTED] Sent: Sunday, March 30, 2008 8:32 PM To: CF-Talk Subject: Re: inefficient query the following being my query... am i trying to do too much with one query? <cfquery name="getProduct" datasource="#variables.dsn#" password="#variables.DBpassword#" username="#variables.DBusername#"> SELECT product.*, product_description.*, product_options.*, categories_description.cat_title FROM product INNER JOIN product_description ON product.product_id = product_description.product_id INNER JOIN categories ON product.cat_id = categories.cat_id INNER JOIN categories_description ON categories.cat_id = categories_description.cat_id LEFT OUTER JOIN artist ON product.artist_id = artist.artist_id LEFT OUTER JOIN product_to_product_options ON product.product_id = product_to_product_options.product_id LEFT OUTER JOIN product_options ON product_to_product_options.product_options_id = product_options.product_options_id WHERE 1=1 <cfif isDefined("arguments.product_id")> AND product.product_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.product_id#"> </cfif> <cfif isDefined("arguments.cat_id")> AND product.cat_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.cat_id#"> </cfif> ORDER BY #arguments.order_key# </cfquery> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302332 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
