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

Question

the MySQL docs say: "You cannot refer to a TEMPORARY table more than once in the same query."

I know this has been asked before. But I can't find a specific solution for the following.

I'm doing a preselection into a temporary table

CREATE TEMPORARY TABLE preselection AS SELECT ...;

now I wanna do some (around 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 preselection and do each select on each table but if I understand it right this is the same as invoke the preselection-query above in every SELECT inside the UNION chain as a subquery.

Is there a way to work around this issue?

Greetings,

chris

Full query:

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

I think the error message is clear: you can't do that with a single temporary table. Does creating a view of the data, instead of a temporary tables, do the trick?

Views in mysql