need some SQL help

From: howzat <dave_at_dave.net>
Date: Fri, 23 Dec 2005 18:45:55 GMT
Message-ID: <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'),