mysql sub-select work around?

[plug-discuss@lists.plug.phoenix.az.us]

Mike,
see below


On 5 Feb 2003 at 11:36, Mike Starke wrote:

> On Wed, Feb 05, 2003 at 09:18:59AM -0700, az_pete@cactusfamily.com wrote:
> /_Hi Mike,
> /_I think this is what you're looking for.
> /_
> /_
> /_drop table if exists tmp_idt;
> /_
this simply drops the table if it exists, to prevent the create temporary table statement from failing if the table 
already exists.

> /_create temporary table tmp_idt
> /_SELECT (idt.unit * idt.price) AS Sub_Total
> /_FROM idt WHERE idt.myid = "1";
> /_
MySQL allows you to create a temp table and populate with the results of a select in one shot.  That's what this 
statement does.  If the 'create temporary table' statement is immediately followed by a select statement, the results 
of the select statement are populated into the the table using the columns names & data types returned from the select 
statement.
Alternativley you could to this:
create temporary table tmp_idt (
	unit	int 	not null,
	price	int	not null
);
insert into tmp_idt
select (idt.unit * idt.price) as sub_total
from idt where idt.myid="1"

But with this method you must explicitly define the table.  This can be a pain since you must know exactly the data 
types and lengths needed to build the temp table.  The advantage to this method is that you can expicitly define 
indexes and other constraints on the temp table to increase access speed.  When you dynamically create the temp table 
(as in the 1st example) *only* the data types are created, there are no indexes put on the temp table even if there 
were indexes on the original source tables.

As an aside, if the column idt.myid is an int datatype you don't need to quote it.  If it's an integer and you quote 
it, MySQL must do an implicit data type conversion, which may slow things down a bit.  If the myid column is a varchar 
data type then you must quote it.

> /_select sum(Sub_Total) as 'total' from tmp_idt;
> /_
You may now manipulate the tmp_idt table the same as any other table.  Note that as soon as you kill your client 
connection the temp table is destroyed.  If you like, you can explicitly drop the table like this:
drop table tmp_idt;


> /_
> Yes, this works. Could you explain to me how the values
> are inserted into the temporary table? All I see is a 'create table'
> statement. How is the table populated with values to be able to
> issue the second select statement?
> 
> Thanks for the solution, now I need to understand how it works :-)_
> 
Hope you found this helpful.  If you have more questions, let me know.

Peter

> v/r
> -Mike
> 
> 
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change  you mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
> 
>