Re: need some SQL help

"howzat" <dave_at_dave.net> wrote in message news:DBXqf.18033$b4.2623_at_newsfe1-win.ntli.net...
> Hi all
>
> I'm having some trouble figuring this out so I'd appreciate if someone
could
> point me in the right direction. (simplified schema appears below)
>
> I have an orders table which holds information about the type of order
> (phone, web, retail etc) and the amount of the order. The orders table is
> linked to a customers table via a customer_id column.
>
> I need to run a query which will return customers who match multiple order
> types and values e.g. all customers who spent more than 200 via mail order
> and more than 100 on the web
>
> The only way I've been able to do this is using subqueries, one for each
> order_type+amount condition. This works great on a small database but the
> live database will have about 30000 rows in the orders table and users
will
> want to run a query on multiple order_type+amount conditions.
>
> thanks
> David
>
> /*
> SQLyog - Free MySQL GUI v5.0
> Host - 5.0.16-nt : Database - toystore
> *********************************************************************
> Server version : 5.0.16-nt
> */
>
>
> create database if not exists `toystore`;
>
> USE `toystore`;
>
> /*Table structure for table `customers` */
>
> DROP TABLE IF EXISTS `customers`;
>
> CREATE TABLE `customers` (
> `id` mediumint(9) NOT NULL auto_increment,
> `name` varchar(255) default NULL,
> PRIMARY KEY (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> /*Data for the table `customers` */
>
> insert into `customers` values
>
> (1,'Tony Blair'),
>
> (2,'John Prescott'),
>
> (3,'Ken Livingstone'),
>
> (4,'David Frost'),
>
> (5,'Margaret Thatcher');
>
> /*Table structure for table `order_types` */
>
> DROP TABLE IF EXISTS `order_types`;
>
> CREATE TABLE `order_types` (
> `id` mediumint(9) NOT NULL auto_increment,
> `description` varchar(255) default NULL,
> PRIMARY KEY (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> /*Data for the table `order_types` */
>
> insert into `order_types` values
>
> (1,'Retail'),
>
> (2,'Phone'),
>
> (3,'Web'),
>
> (4,'Mail Order'),
>
> (5,'Third Party');
>
> /*Table structure for table `orders` */
>
> DROP TABLE IF EXISTS `orders`;
>
> CREATE TABLE `orders` (
> `id` mediumint(9) NOT NULL auto_increment,
> `customer_id` mediumint(9) default NULL,
> `order_type` smallint(6) default NULL,
> `amount` decimal(8,2) default NULL,
> PRIMARY KEY (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
>
> /*Data for the table `orders` */
>
> insert into `orders` values
>
> (1,1,1,'300.00'),
>
> (2,2,2,'22.00'),
>
> (3,2,4,'27.50'),
>
> (4,1,4,'30.00'),
>
> (5,5,3,'65.00'),
>
> (6,5,3,'125.00'),
>
> (7,1,1,'325.00'),
>
> (8,4,5,'62.00'),
>
> (9,4,1,'300.00'),
>
> (10,1,4,'254.00'),
>
> (11,1,5,'39.00'),
>
> (12,2,2,'558.00'),
>
> (13,4,3,'13.00'),
>
> (14,3,1,'362.00'),
>
> (15,3,2,'355.00'),
>
> (16,3,1,'18.00');
>
>
>
Received on Fri Dec 23 2005 - 21:47:41 CET