Hi Shawn: I tried to run the sequences as you suggested in MySql Query Browser. I ran the first part with the create temp table then I ran the second select to see the result but when I tried the second SQL to get the created rows I get the message 'Table xxxx.tmpTotals doesn't exist' where xxxx=schema name.
In additon, THANK you for taking the time to clarify the confusion about connections .. you are a wealth of information. best regards Imran ----- Original Message ----- From: <[EMAIL PROTECTED]> To: "Imran" <[EMAIL PROTECTED]> Cc: <mysql@lists.mysql.com> Sent: Tuesday, October 11, 2005 11:33 AM Subject: Re: Help on writing a sql statement > Will you have name collisions with the same temporary table names used > from different connections? Nope. > > Temporary tables and user-defined (@-variables) are connection-specific. > Even if the same username/password combination is used to create several > connections, each connection will have its own set of user-defined > variables and temporary tables. What may get confusing is if you are using > a connection pool manager (like ODBC) and you pick up a connection that > you thought was "new" but was really just "recycled". > > When you request a connection and close one and you are behind a > connection pool manager, the manager doesn't actually create and destroy > new connections each time. It merely loans you one it already has open and > it will open or close the connections as it sees fit. When you try to > close the connection, all you are really doing is telling the manager that > it is OK for some other thread/process to use it. So long as you do not > assume a variable to have a particular value unless you set it to be > something (do not assume that a variable you haven't set is still null) > and so long as you destroy any temporary tables when you are through using > them, you shouldn't run into any "inheritance" problems from thread to > thread. > > The good thing is that in the case of ODBC (at least on Win32) you can > decide for each driver if you want the ODBC connection manager to pool > connections or not. > > Make sense? > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > "Imran" <[EMAIL PROTECTED]> wrote on 10/11/2005 11:19:20 AM: > > > Hi Shawn: > > > > Thank you very much for your solution. It certainly helped me in > > understanding SQL a whole deal more. > > > > I Have one followup question as to the proposed solution, This query > will be > > used in a multi-user situation and the logon to the database will be the > > same user (a web based app) ... Since I am creating a temp table, will > the > > temp table creation fail for subsequent users prior to the drop i.e. the > > table will exist already exist .... > > > > > > best regards > > Imran. > > > > ----- Original Message ----- > > From: <[EMAIL PROTECTED]> > > To: "Imran" <[EMAIL PROTECTED]> > > Cc: <mysql@lists.mysql.com> > > Sent: Tuesday, October 11, 2005 9:49 AM > > Subject: Re: Help on writing a sql statement > > > > > > > (my response bottom-posted. See below - SG) > > > > ----- 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 > > > > > > > > > > > Let me see if I can translate what you want in a query into regular > > > language. I think you would like to see, grouped by date, customer, > and > > > product, the total cost and total sales for each > (date,customer,product) > > > triple along with each product's description ,code, and the customer's > > > number and name. All of that will be limited to activity on or before > > > midnight of a certain date. > > > > > > If I rephrased that correctly, here is how I would build your query. > Step > > > 1 is to perform the (date,customer,product) summations. By minimizing > the > > > number of rows, columns, and/or tables we need to summarize against, > we > > > improve performance. So I do this part of the analysis before I join > in > > > the other tables. > > > > > > Note: Date, time, and datetime literals are represented by > single-quoted > > > strings. You do not need the DATE() function to create a date literal. > > > > > > > > > CREATE TEMPORARY TABLE tmpTotals ( > > > key(CustNo) > > > , key(ProdNo) > > > ) > > > SELECT PostingDate > > > , CustNo > > > , ProdNo > > > , sum(Cost) as costs > > > , sum(Sales) as sales > > > FROM salesmaster > > > WHERE PostingDate <= '2005-09-01 00:00:00' > > > GROUP BY PostingDate, CustNo, ProdNo; > > > > > > Step 2: collect the rest of the information for the report. > > > SELECT CustNo > > > , c.Name as custname > > > , ProdNo > > > , p.Name as prodname > > > , costs > > > , sales > > > , PostingDate > > > FROM tmpTotals tt > > > LEFT JOIN customerintermediate c > > > ON c.CustNo = tt.CustNo > > > LEFT JOIN productintermediate p > > > ON p.ProdNo = tt.ProdNo > > > ORDER BY ... your choice... ; > > > > > > Step 3: The database is not your momma. Always clean up after > yourself. > > > > > > DROP TEMPORARY TABLE tmpTotals; > > > > > > And you are done! The only trick to doing a sequence of statements in > a > > > row (like this) is that they all have to go through the same > connection. > > > As long as you do not close and re-open the connection between > statements, > > > any temp tables or @-variables you create or define remain in > existence > > > for the life of the connection. Depending on your connection library, > you > > > might be able to execute all three statements from a single request. > Most > > > likely, you will need to send them in one-at-a-time. > > > > > > Does this help you to organize your thoughts? > > > > > > 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]
