Re: Help on writing a sql statement

[Imran]

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: <SGreen@stripped>
To: "Imran" <imran@stripped>
Cc: <mysql@stripped>
Sent: Monday, October 10, 2005 4:17 PM
Subject: Re: Help on writing a sql statement


> "Imran" <imran@stripped> 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
>
>
>