thanks josh and rick. my query works great.
now...
the client wishes to know those customers who have ordered (separate invoices)
more than once (again of a particular category).
i have managed the following...
<cfquery>
SELECT dbo.orders.dCreated, dbo.orders.order_id, dbo.address_book.customer_id,
dbo.address_book.customer_fname, dbo.address_book.customer_lname,
COUNT(dbo.products.product_id) AS 'product_count'
FROM dbo.address_book
INNER JOIN dbo.orders ON dbo.address_book.customer_id = dbo.orders.customer_id
INNER JOIN dbo.orders_products ON dbo.orders.order_id =
dbo.orders_products.order_id
INNER JOIN dbo.products ON dbo.orders_products.product_id =
dbo.products.product_id
INNER JOIN dbo.products_to_categories ON dbo.products_to_categories.products_id
= dbo.products.product_id
INNER JOIN dbo.categories ON dbo.products_to_categories.cat_id =
dbo.categories.cat_id
WHERE (dbo.categories.cat_id = 1 OR dbo.categories.cat_id = 2)
AND orders.order_id IN (
SELECT O.order_id
FROM orders AS O
WHERE EXISTS (
SELECT customer_id, COUNT(order_id) FROM orders
WHERE orders.customer_id = O.customer_id
GROUP BY orders.customer_id
HAVING COUNT(orders.order_id) > 1
)
)
GROUP BY dbo.orders.dCreated, dbo.orders.order_id,
dbo.address_book.customer_id, dbo.address_book.customer_fname,
dbo.address_book.customer_lname
ORDER BY dbo.address_book.customer_lname, dbo.address_book.customer_fname,
dbo.orders.dCreated ASC
</cfquery>
seems to work ok. basically it shows each customer and each of their separate
orders (if they have more than one).
for some reason however, some customers are only showing one order (even though
they have ordered more than once).
brain overload !!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade to ColdFusion 8 and integrate with Adobe Flex
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:286913
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
