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" <imran@stripped> 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: <SGreen@stripped>
> To: "Imran" <imran@netwave.ca>
> Cc: <mysql@stripped>
> 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" <imran@stripped> 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: <SGreen@stripped>
> > > To: "Imran" <imran@netwave.ca>
> > > Cc: <mysql@stripped>
> > > 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: <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
> > > > > >
> > > >
> > > > 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
> > > >
> > >
> > >
> >
> >
>
>
