Self-joins on derived tables in mysql- do i need to repeat the subqueries?

Question

I've got to execute a self-joining sql statement on a derived table in mysql. The derived table involves a hairy subquery, and I'm wondering if there's any alternative to actually writing and executing it twice-

SELECT a.* FROM (my hairy subquery) AS a
  LEFT JOIN (my hairy subquery) AS a2 
    ON a.groupname = a2.groupname etc..

Answer 1

The standard solution to this is to use CTEs, but these are not yet supported in MySQL. Alternatives are:

• You can put your subquery in a view and self-join the view.
• You can create a temporary table and populate it with the results of your subquery.

Related

• How do you use the "WITH" clause in MySQL?