title: How can I refer to a TEMPORARY table more than once in the same query?
body: <p>the MySQL docs say: "You cannot refer to a TEMPORARY table more than once in the same query."</p>
  
  <p>I know this has been asked before. But I can't find a specific solution for the following.</p>
  
  <p>I'm doing a preselection into a temporary table</p>
  
  <pre><code>CREATE TEMPORARY TABLE preselection AS SELECT ...;
  </code></pre>
  
  <p>now I wanna do some (around 20 or even 30) unions</p>
  
  <pre><code>(SELECT FROM preselection ...)
  UNION
  (SELECT FROM preselection ...)
  UNION
  ......
  UNION
  (SELECT FROM preselection ...)
  </code></pre>
  
  <p>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.</p>
  
  <p>Is there a way to work around this issue?</p>
  
  <p>Greetings,</p>
  
  <p>chris</p>
  
  <p>Full query:</p>
  
  <pre><code>CREATE TEMPORARY TABLE preselection AS
  (
  SELECT id, title, chapter, date2, date, snid, max(score) FROM `movies`
  
  WHERE 
  (
  cluster is not NULL
  ) 
  AND 
  (
  `date` &lt;= '2012-02-20 05:20:00'
  AND `date` &gt; '2012-02-19 17:20:00' 
  AND (TIMEDIFF(date, date2) &lt; '12:00:00')
  )
  GROUP BY cluster
  )
  UNION
  (
  SELECT id, title, chapter, date2, date, snid, score FROM `movies`
  WHERE cluster IS NULL
  AND
  (
  `date` &lt;= '2012-02-20 05:20:00' AND `date` &gt; '2012-02-19 17:20:00' AND (TIMEDIFF(date, date2) &lt; '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`;
  </code></pre>