Accessing a temporary table multiple times in MySql

Question

I have tried to use a temporary table as an intermediate result holder for a SELECT statement. The problem is though that I can't access the temp table multiple times in other queries statement which I hoped would be possible i.e. makes the temp table useless.

Is there an alternative to temporary tables in MySql that allows me to extract my SQL statement.

I can't use store procedures (can't access them from the web-framework version used in the company) and I don't want to use a cursor.

Edit:

Well my code looks somewhat like this:

Temp table creation:

CREATE TEMPORARY TABLE dates_with_entries (
  seq  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  datum VARCHAR(32)
);

INSERT INTO dates_with_entries (datum) SELECT datum AS Datum from project_times
    WHERE user_id = 20 GROUP BY datum ORDER BY datum desc LIMIT 13;

Then the code where I use my temp table looks somewhat like this (I simpled it down to the problem I have..)

SELECT 
...
FROM (SELECT entrie_date AS datum FROM dates_with_entries ) AS sub_result
INNER JOIN project_times
    ON sub_result.datum = project_times.datum AND project_times.user_id = 20
LEFT JOIN works AS w ON project_times.work_id = w.id
LEFT JOIN sub_projects AS sp ON sp.id = w.sub_project_id
LEFT JOIN projects AS p ON p.id = sp.project_id
GROUP BY datum
UNION(
    SELECT
      ..
  FROM (SELECT entrie_date AS datum FROM dates_with_entries ) AS sub_result
  INNER JOIN project_times AS pt ON pt.datum = sub_result.datum
  INNER JOIN works AS w on w.id = pt.work_id
  INNER JOIN sub_projects AS sp on w.sub_project_id = sp.id
  INNER JOIN projects AS p ON sp.project_id = p.id
  WHERE pt.user_id = 20
);

The numbers will be later on replaced by ruby, this is only for testing the SQL statement.

Answer 1

One way around this is to simply create a "real" table, rather than a temporary table.

The advantages that temporary tables offer:

1. Namespacing. You can create multiple temporary tables with the same name in separate sessions.
2. Automatic cleanup. You don't need explicitly drop the table when you're done with it. It goes away when you end your session

If #1 is crucial for you, then you probably need to stick with temporary tables. Otherwise, if only one instance of this program runs at a time, or if you create the table name dynamically to make it unique, I recommend that you choose an appropriately unique name for this table and create it as a "real" table, then drop it when you're done.

Answer 2

Known bug (feature) - read http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html

Here's how i work around the problem...

drop table if exists employees;
create table employees
(
emp_id smallint unsigned not null auto_increment primary key,
name varchar(255) not null,
boss_id smallint unsigned null,
key (boss_id)
)
engine = innodb;

insert into employees (name, boss_id) values
('f00',null), 
  ('ali later',1), 
  ('megan fox',1), 
      ('jessica alba',3), 
      ('eva longoria',3), 
         ('keira knightley',5), 
            ('liv tyler',6), 
            ('sophie marceau',6);


drop procedure if exists employees_hier;

delimiter #

create procedure employees_hier
(
in p_emp_id smallint unsigned
)
begin

declare v_done tinyint unsigned default(0);
declare v_dpth smallint unsigned default(0);

create temporary table hier(
 boss_id smallint unsigned, 
 emp_id smallint unsigned, 
 depth smallint unsigned
)engine = memory;

insert into hier select boss_id, emp_id, v_dpth from employees where emp_id = p_emp_id;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table emps engine=memory select * from hier;

while not v_done do

    if exists( select 1 from employees e inner join hier on e.boss_id = hier.emp_id and hier.depth = v_dpth) then

        insert into hier select e.boss_id, e.emp_id, v_dpth + 1 
            from employees e inner join emps on e.boss_id = emps.emp_id and emps.depth = v_dpth;

        set v_dpth = v_dpth + 1;            

        truncate table emps;
        insert into emps select * from hier where depth = v_dpth;

    else
        set v_done = 1;
    end if;

end while;

select 
 e.emp_id,
 e.name as emp_name,
 p.emp_id as boss_emp_id,
 p.name as boss_name,
 hier.depth
from 
 hier
inner join employees e on hier.emp_id = e.emp_id
left outer join employees p on hier.boss_id = p.emp_id;

drop temporary table if exists hier;
drop temporary table if exists emps;

end #

delimiter ;

-- call this sproc from your php

call employees_hier(1);