0

I have a system that generates a SQL query with lots of derived table within sub queries. Here is a typical example:

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`

What I would like to do it be able to format it as such:

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` 

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 temp_table_field1.

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.

Perhaps this question is, can I derive a table from a derived table?

BTW, my sql works and executes fine by having lots of derived tables, I just wonder whether or not:

  1. it is quicker to be able to assign once, use many times
  2. it makes any particular difference

ALSO NOTE: I cannot assign these as temporary tables. It has to execute within one query.

Svante
  • 50,694
  • 11
  • 78
  • 122
  • "my sql works and executes fine", are you sure about that regarding what you've posted? `temp_table_main.id` does not exist and it is the on the second line of both versions you've posted. – Uueerdo Oct 12 '17 at 17:02
  • Hi there, yes I'm sure - `id` is not in the select clauses, but it does exist on the table. I'll modified to suit. – E R Barratt Oct 12 '17 at 17:06
  • If it is not in the SELECT clauses of the subqueries, it will not be available outside of them. – Uueerdo Oct 12 '17 at 17:07
  • Modified - I'm anonymising from a production query. The id field refers to the company name so I had taken it out! – E R Barratt Oct 12 '17 at 17:08

0 Answers0