We have the three tables below and for each customer, we need the
following information;
Result expected:
Month | Sales total | Payment | Oustanding
Jan | 1200.99 | 200.99 | 1000
Feb | 0.00 | 1000.00 | 0
Can anyone help out with the sql query to generate this.
Thanks
Barry
CREATE TABLE `order_details` (
`order_details_id` int(50) NOT NULL auto_increment,
`order_id` varchar(255) NOT NULL default '',
`product_id` varchar(255) NOT NULL default '',
`quantity` decimal(11,2) NOT NULL default '0.00',
`rate` decimal(11,2) NOT NULL default '0.00',
`datecreated` date default '0000-00-00',
PRIMARY KEY (`order_details_id`),
UNIQUE KEY `order_details_id` (`order_details_id`),
KEY `order_details_id_idx` (`order_details_id`),
KEY `order_id_idx` (`order_id`)
) TYPE=MyISAM;
CREATE TABLE `orders` (
`orderid` int(11) NOT NULL auto_increment,
`invoice_id` varchar(255) NOT NULL default '',
`page_no` varchar(255) NOT NULL default '',
`custid` varchar(255) NOT NULL default '',
`driver_id` varchar(255) NOT NULL default '',
`datecreated` date NOT NULL default '0000-00-00',
PRIMARY KEY (`orderid`),
UNIQUE KEY `orderid` (`orderid`),
KEY `orders_id_idx` (`orderid`),
KEY `orders_invoice_id_idx` (`invoice_id`)
) TYPE=MyISAM;
CREATE TABLE `payment` (
`payment_id` int(11) NOT NULL auto_increment,
`payment_amount` decimal(11,2) NOT NULL default '0.00',
`payment_date` date default NULL,
`payment_method` varchar(25) default NULL,
`custid` int(11) NOT NULL default '0',
`driver_id` varchar(255) default NULL,
`inv_id` varchar(255) NOT NULL default '0',
`page_no` int(11) NOT NULL default '0',
PRIMARY KEY (`payment_id`),
UNIQUE KEY `payment_id` (`payment_id`),
KEY `payment_id_idx` (`payment_id`)
) TYPE=MyISAM;