"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
