Hi Jim,
Jim Wright wrote: >On 1/23/08, Ben Conner <[EMAIL PROTECTED]> wrote: > > >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? > > Probably not. This was inherited from an old application several years ago. It -really- needs help or just replacing the entire thing with a new system. But I was taught a long time ago (under threat of jail--literally) "If it ain't broke, don't fix it.". Of course, the application was the nuclear war plan, so they get a little hinky about stuff like that. ;-) --Ben +-------------------------------------------+ + Ben Conner [EMAIL PROTECTED] + + Web World, Inc. 888-206-6486 + + PO Box 1122 480-704-2000 + + Queen Creek, AZ 85242 + +-------------------------------------------+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:297212 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
