How can I refer to the TEMPORARY table more than once in a single query?

Question

MySQL docs say, "You cannot reference a TEMPORARY table more than once in a single query."

I know this was asked before. But I can not find a specific solution for the following.

I am preselecting a temporary table

CREATE TEMPORARY TABLE preselection AS SELECT ...;

Now I want to make several (about 20 or even 30) unions

(SELECT FROM preselection ...)
UNION
(SELECT FROM preselection ...)
UNION
......
UNION
(SELECT FROM preselection ...)

I could make 20 or 30 copies of the preselect and make each selection in each table, but if I understand correctly, this is the same as the preselection query above in each SELECT inside the UNION chain as a subquery.

Is there a way around this problem?

Hi,

Chris

Full request:

CREATE TEMPORARY TABLE preselection AS
(
SELECT id, title, chapter, date2, date, snid, max(score) FROM `movies`

WHERE 
(
cluster is not NULL
) 
AND 
(
`date` <= '2012-02-20 05:20:00'
AND `date` > '2012-02-19 17:20:00' 
AND (TIMEDIFF(date, date2) < '12:00:00')
)
GROUP BY cluster
)
UNION
(
SELECT id, title, chapter, date2, date, snid, score FROM `movies`
WHERE cluster IS NULL
AND
(
`date` <= '2012-02-20 05:20:00' AND `date` > '2012-02-19 17:20:00' AND (TIMEDIFF(date, date2) < '12:00:00')
)
);

(SELECT * FROM preselection WHERE snid=1 AND chapter LIKE '#A_OT%'
 DESC LIMIT 4)
UNION
 … 
UNION 
(SELECT * FROM preselection WHERE snid=19 AND chapter LIKE '#A_OT%' 
 LIMIT 4)
UNION
 ... for each chapter from A to J and every snid from 1 to 19 ...
UNION
(SELECT * FROM preselection WHERE snid=1 AND chapter LIKE '#J_OT%'
 LIMIT 4)
UNION
 … 
UNION 
(SELECT * FROM preselection WHERE snid=19 AND chapter LIKE '#J_OT%' 
LIMIT 4)

ORDER BY `score` DESC, `date`;

Answer 1

, : . ?

mysql

Answer 2

, . , , .

(, , )

CREATE TEMPORARY TABLE preselectionCopy AS
SELECT * FROM preselection;

CREATE TEMPORARY TABLE result (
    id INT NULL,
    title VARCHAR(256) NULL
    ...
    );

..., ,

CREATE TEMPORARY TABLE result AS
SELECT * FROM preselection WHERE snid=1 AND chapter LIKE '#A_OT%' 
LIMIT 4;

INSERT INTO result
SELECT * FROM preselection WHERE snid=19 AND chapter LIKE '#A_OT%' 
 LIMIT 4;

...

SELECT * FROM result 
ORDER BY `score` DESC, `date`;