The CREATE TABLE ... SELECT ... command is all one command. You weren't supposed to break it up. Sorry if I didn't make that very clear (my fault!!).
You should probably be on a SCRIPT tab not a QUERY tab if you are running this through QueryBrowser in order to execute more than one statement at a time. I don't use it very often but I think that QB doesn't maintain connections between calls on the same tab (can't remember and can't test right now) and IIRC, the QUERY tabs only take one command at a time. Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Imran" <[EMAIL PROTECTED]> wrote on 10/11/2005 12:00:25 PM: > 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 > > > > > > > > > > > > > > > >
