This will not be exact but should get you close...tweak as needed SELECT customer_fname, customer_lname FROM order_products INNER JOIN orders ON order_products.order_id = orders.order_id INNER JOIN address_book ON orders.customer_id = address_book.customer_id WHERE order_products.product_id IN (SELECT product_id FROM productsXcategories WHERE category_id = [desired category here]) GROUP BY order_products.product_id HAVING Count(product_id) > 1
-- Josh ----- Original Message ----- From: "Mike | NZSolutions Ltd" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: Wednesday, August 22, 2007 3:02 PM Subject: Complex query for experts? > Well I think it is !! > > Ok, my client wishes to know the following information... > > Which customers have ordered more than one clothing item (of a particular > category) in a single order. > > Eg. joe bloggs ordered 3 jerseys on 3/4/07. > > The relevant tables and columns are... > > CREATE TABLE [dbo].[categories] ( > [cat_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL > ) > > CREATE TABLE [dbo].[products] ( > [product_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL > ) > > CREATE TABLE [dbo].[orders] ( > [order_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , > [dCreated] [datetime] NULL , > [customer_id] [int] NULL > ) > > CREATE TABLE [dbo].[orders_products] ( > [order_products_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT > NULL , > [order_id] [int] NOT NULL , > [product_id] [int] NOT NULL , > [product_code] [varchar] (50) COLLATE Latin1_General_CI_AS NULL , > [product_title] [varchar] (64) COLLATE Latin1_General_CI_AS NOT NULL > , > [product_quantity] [int] NOT NULL > ) > > (note: some orders have the same item in 2 different rows eg. product_id = > 1 > / product_quantity = 2 and product_id = 1 / product_quantity = 1) > > CREATE TABLE [dbo].[address_book] ( > [customer_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL , > [customer_fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL , > [customer_lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL > ) > > Any pointers on how I should go about this would really be appreciated. > > Cheers > mike > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Enterprise web applications, build robust, secure scalable apps today - Try it now ColdFusion Today ColdFusion 8 beta - Build next generation apps Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:286894 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
