title: MySQL Using Derived Tables WITHIN subQueries without re-defining them
body: <p>I have a system that generates a SQL query with lots of derived table within sub queries. Here is a typical example:</p>
  
  <pre><code>SELECT
  
      COUNT(`temp_table_main`.`person_id`) AS "resp_count",
      `temp_table_main`.`SAMPLE`, 
      SUM(`temp_table_main`.`WT`) AS "resp_count_weight" ,
      SUM(`temp_table_main`.`field1`) as `field1_count`,
      (
          SELECT 
              SUM(`temp_table_field1`.`WT`) 
          FROM 
              (
                  SELECT
                          `person_id`,
                      MAX(CASE when `variable` = 4 THEN `value` END) AS `field1`,
                      MAX(CASE when `variable` = 10 THEN `value` END) AS `WT`,
                      MAX(CASE when `variable` = 3 THEN `value` END) AS `SAMPLE`
  
                  FROM
                      `4_86_data`
                  GROUP BY `person_id`
              ) AS `temp_table_field1`
  
          WHERE `temp_table_field1`.`field1` = 1
          AND `SAMPLE` = `temp_table_main`.`SAMPLE`
  
      ) AS `field1_count_WT`
  FROM
      (
          SELECT
                  `person_id`,
              MAX(CASE when `variable` = 4 THEN `value` END) AS `field1` ,
              MAX(CASE when `variable` = 3 THEN `value` END) AS `SAMPLE` ,
              MAX(CASE when `variable` = 10 THEN `value` END) AS `WT` 
          FROM
              `4_86_data`
          GROUP by `person_id`
      ) AS `temp_table_main`
  WHERE (  `field1` IS NOT NULL )  GROUP BY `SAMPLE`
  </code></pre>
  
  <p>What I would like to do it be able to format it as such:</p>
  
  <pre><code>SELECT
      COUNT(`temp_table_main`.`person_id`) AS "resp_count",
      `temp_table_main`.`SAMPLE`, 
      SUM(`temp_table_main`.`WT`) AS "resp_count_weight" ,
      SUM(`temp_table_main`.`field1`) as `field1_count`,
      (
          SELECT 
              SUM(`temp_table_field1`.`WT`) 
          FROM 
              `temp_table_main` AS `temp_table_field1`
          WHERE `temp_table_field1`.`field1` = 1
          AND `SAMPLE` = `temp_table_main`.`SAMPLE`
  
      ) AS `field1_count_WT`
  FROM
      (
          SELECT
                  `person_id`,
              MAX(CASE when `variable` = 4 THEN `value` END) AS `field1` ,
              MAX(CASE when `variable` = 3 THEN `value` END) AS `SAMPLE` ,
              MAX(CASE when `variable` = 10 THEN `value` END) AS `WT` 
          FROM
              `4_86_data`
          GROUP by `person_id`
      ) AS `temp_table_main`
  WHERE (  `field1` IS NOT NULL )  GROUP BY `SAMPLE` 
  </code></pre>
  
  <p>You'll notice that I have attempted to refer to the outer derived table within a sub-query in the outer select statement, assigning it to <code>temp_table_field1</code>. </p>
  
  <p>There are cases where I will have N number of such sub queries within the outer select statement, hence the need to assign it as a different derived table name.</p>
  
  <p>Perhaps this question is, can I derive a table from a derived table?</p>
  
  <p>BTW, my sql works and executes fine by having lots of derived tables, I just wonder whether or not:</p>
  
  <ol>
  <li>it is quicker to be able to assign once, use many times</li>
  <li>it makes any particular difference</li>
  </ol>
  
  <p>ALSO NOTE: I cannot assign these as temporary tables. It has to execute within one query.</p>