On 1/23/08, Ben Conner <[EMAIL PROTECTED]> wrote: > > SQL = "SELECT Products.*, Categories.*, Manufacturers.* > FROM Products, Categories, Manufacturers > WHERE Products.ManufacturerID IN (Select ManufacturerID from Manufacturers > WHERE Manufacturers.ManufacturerName Like '%spank%') AND > Categories.CategoryID = Products.CategoryID AND > Manufacturers.ManufacturerID = Products.ManufacturerID > ORDER BY Products.ProductName > UNION ALL > SELECT Products.*, Categories.*, Manufacturers.* > FROM Products, Categories, Manufacturers > WHERE Products.ProductID IN (Select ProductID from Products > WHERE Products.ProductName Like '%spank%' ) AND > Categories.CategoryID = Products.CategoryID AND > Manufacturers.ManufacturerID = Products.ManufacturerID > ORDER BY Products.ProductName" > > This query runs fine when the target database is Access. Anyone have a > suggestion how to rephrase it or a syntax that works?
It is the ORDER BY in the first query that is throwing the errors. Just use a single ORDER BY after all of the UNIONs, and it will order the entire set. Also, do you mean to use UNION ALL? That will result in duplicate rows if a productname and manufacturename both contain...ummm..."spank". If you don't need the duplicate rows, you could rewrite this: SELECT p.*, c.*, m.* FROM Products p INNER JOIN Categories c ON p.CategoryID = c.CategoryID INNER JOIN Manufacturers m ON p.ManufacturerID = m.ManufacturerID WHERE p.ProductName LIKE '%spank%' OR m.ManufacturerName LIKE '%spank%' ORDER BY p.productname And, of course, it would be better to specify the columns that you pull back rather than use a wildcard. Do you really need all of the data from all tables? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297114 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
