Hi Shawn .. Thanks for the offer. I am attaching the info that you
requested.
mysql> SHOW CREATE TABLE salesmaster\G;
*************************** 1. row ***************************
Table: salesmaster
Create Table: CREATE TABLE `salesmaster` (
`ID` int(10) NOT NULL auto_increment,
`PostingDate` datetime default NULL,
`PostingDateJulian` double(15,5) default NULL,
`CustNo` varchar(10) default NULL,
`ProdNo` varchar(5) default NULL,
`SalesMan` char(2) default NULL,
`Branch` int(10) default NULL,
`Kilos` decimal(19,4) default '0.0000',
`Sales` decimal(19,4) default '0.0000',
`Cost` decimal(19,4) default '0.0000',
PRIMARY KEY (`ID`),
KEY `PostingDate` (`PostingDate`),
KEY `CustNo` (`CustNo`),
KEY `ProdNo` (`ProdNo`),
KEY `Branch` (`Branch`)
)
mysql> SHOW CREATE TABLE customerintermediate\G;
*************************** 1. row ***************************
Table: customerintermediate
Create Table: CREATE TABLE `customerintermediate` (
`ID` int(10) NOT NULL auto_increment,
`CustNo` varchar(10) default NULL,
`CustName` varchar(120) default NULL,
`CustShipAdd1` varchar(50) default NULL,
`CustShipAdd2` varchar(50) default NULL,
`CustShipAdd3` varchar(50) default NULL,
`CustShipPostal` varchar(50) default NULL,
`Dept` int(10) default NULL,
`Class` int(10) default NULL,
`Branch` int(10) default NULL,
PRIMARY KEY (`ID`),
KEY `CustNo` (`CustNo`),
KEY `Branch` (`Branch`),
KEY `Dept` (`Dept`)
)
mysql> SHOW CREATE TABLE productintermediate\G;
*************************** 1. row ***************************
Table: productintermediate
Create Table: CREATE TABLE `productintermediate` (
`ID` int(10) NOT NULL auto_increment,
`ProdNo` varchar(5) default NULL,
`Description` varchar(255) default NULL,
`Status` smallint(5) default NULL,
`Branch` int(10) default NULL,
`Source` int(10) default NULL,
`Main` int(10) default NULL,
`Report` int(10) default NULL,
PRIMARY KEY (`ID`),
KEY `ProdNo` (`ProdNo`),
KEY `Branch` (`Branch`)
)
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: "Imran" <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Monday, October 10, 2005 4:17 PM
Subject: Re: Help on writing a sql statement
> "Imran" <[EMAIL PROTECTED]> wrote on 10/10/2005 03:52:21 PM:
>
> > Hi all:
> > I need some help in writing a sql statement.
> >
> > I have three tables (Sales, Cust and Product). The sales table contains
> a
> > large volume of data and I want to create a sql to group the sales table
> > then join the resultant to both the Cust and Prod and to have additional
> > fields selected from the Cust and Prod.
> >
> > So in effect something like (obviously syntax is wrong)
> >
> > Select sm.prodno, sm.custno, sum(cost) as costs, sum(sales) as sales,
> > sm.date
> >
> > from Sales SM where sm.date <= date(‘2005-09-01 00:00:00’) Group by
> > sm.prodno, sm.custno, sm.date ,
> >
> > (Select prod.desc, prod.code, cust.custno, cust.name from cust, Prod)
> left
> > join sm.prodno = prod.code left join sm.custno=cust.custno) …
> >
> > Any help would be greatly appreciated.
> >
>
> OK, you know you need a GROUP BY, that's good. You also recognized you
> needed to JOIN a few tables together, also good. There are at least two
> ways to do what you ask. One is a fairly complex query that does it all in
> one statement (might take a long time to compute) the other is a sequence
> of two simpler statements. I think the two-statement solution will be
> easier to understand and maintain so I would prefer to go over that.
> However, in order to provide an example of either method I will need more
> information from you.
>
> From the CLI (command line client), please provide the output from these
> three commands:
>
> SHOW CREATE TABLE sales\G;
> SHOW CREATE TABLE cust\G;
> SHOW CREATE TABLE product\G;
>
> That will tell me exactly which columns live on which tables and where you
> do or do not have any indexes. Good indexes will make or break the
> performance of your database. You will not be exposing any data, only the
> design of the tables.
>
> Please remember to CC the list on all responses.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
