need some SQL help

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');



.

Relevant Pages

  • Re: Primary Key Dilemma
    ... > I'm designing an application that will be sold to multiple customers, each> of which is a small business. ... The database behind this application will be> accessed via the Internet, and will be shared by *all* customers. ... Each> business is permitted to access only its own data. ... It just doesn't seem right to me to have an auto inc column AND a> separate business_id column comprising the primary key. ...
    (microsoft.public.sqlserver.programming)
  • RE: Is Access the right app (newbie question)?
    ... Products, containing ProductID (primary key), Size, Model, etc. fields ... Customers, containing CustomerID, Address, etc. fields ... to record who makes/edits each quote) ... to the central database across the network. ...
    (microsoft.public.access.gettingstarted)
  • RE: Store Added Value List Items
    ... You could even union that query with the actual list from the form ... Each user in this database needs to have access to all the same customers. ... is praticle to create a seperate one field table to store values for a lookup ...
    (microsoft.public.access.formscoding)
  • Re: Please suggest relationships model
    ... Providing Customers A Resource For Help With Access, ... However, if he wanted his database quick and painless, I provided ... Here you are in the newsgroup offering services that were NOT ASKED for. ... the newsgroups and a few larger jobs. ...
    (microsoft.public.access.queries)
  • Re: Query to merge
    ... Thanks for that insite - I am sure that the additional customers (that would ... simply build your query to give you all records from the one, ... Say it was a customer database. ... customers with the same primary key. ...
    (microsoft.public.access.queries)