I'm pretty sure that you want count(products.id) not sum. If the product id is 100 then sum(product.id) is going to be 100 even though there is only one record. But Rick knows that, he just needs to fire his typist ;-)
Judah Rick Root wrote: > That should be: > > having > sum(products.id) > 1 > > > On 8/22/07, Rick Root <[EMAIL PROTECTED]> wrote: >> Without knowing a little more about the structure... try something >> like this (I used pseudo code, not actual field names / table names, >> but you should get the idea) >> >> I don't know for sure if this will work but it should drive you in the >> right direction. >> >> First you need a query that produces a list of customer/order/category >> combinations that sums the number of products ordered in each >> category. >> >> Then you restrict the results to return only rows where the sum of >> products per category is more than 1. >> >> select >> orders.customer_id, >> orders.id, >> categories.id, >> sum(products.id) >> from orders >> inner join order_products on order_products.id=orders.id >> inner join products on products.id=orders.id >> inner join categories on categories.id=products.id >> group by >> orders.customer_id, >> orders.id >> having >> sum(categories.id) > 1 >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Check out the new features and enhancements in the latest product release - download the "What's New PDF" now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:286899 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
