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 > > > >
