hi guys, i have a product table with over 500 records in it. each product is linked to a category and brand. when a user inputs a search i need all three elements searched (plus other fields as you will see in query below). at the moment, the query is not the fastest of queries (database is mysql). i was wondering if anyone can see any obvious mistakes i am making with this function...
pid = product id cid = category id bid = brand id <cfquery> SELECT P.pid, P.price, P.price_sale, P.bid, P.display, P.views, PD.ptitle, PD.availability FROM tblProducts P INNER JOIN tblProductDesc PD ON P.pid = PD.pid INNER JOIN tblProductCatLink PCL ON P.pid = PCL.pid INNER JOIN tblCat C ON PCL.cid = C.cid INNER JOIN tblCatDesc CD ON C.cid = CD.cid INNER JOIN tblBrand B ON P.bid = B.bid WHERE ((P.code LIKE '%searchstring%') OR (PD.ptitle LIKE '%searchstring%') OR (PD.pdesc LIKE '%searchstring%') OR (PD.fill_type LIKE '%searchstring%') OR (PD.summary LIKE '%searchstring%') OR (PD.features LIKE '%searchstring%') OR (CD.ctitle LIKE '%searchstring%') OR (B.btitle LIKE '%searchstring%')) AND P.display = 1 GROUP BY P.pid, P.price, P.price_sale, P.bid, P.display, P.views, PD.ptitle, PD.availability ORDER BY PD.ptitle ASC </cfquery> thanks heaps. mike ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316798 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
