SQL query TestLink

gistfile1.txt

SELECT
    NH_TCASE.name                    AS 
    tcase_name,
    NH_TCASE.id                      AS 
    tcase_id,
    NH_TCASE.id                       AS 
    tc_id,
    TPTCV.tcversion_id,
    TCV.version,
    TCV.tc_external_id               AS 
    external_id,
    TCV.execution_type,
    TPTCV.id                         AS 
    feature_id,
    TPTCV.platform_id,
    PLAT.name                         AS 
    platform_name,
    TPTCV.node_order                  AS 
    execution_order,
    COALESCE(E.status,'n')           AS 
    exec_status,
    CONCAT('TT-',TCV.tc_external_id) AS 
    full_external_id 
FROM
    testplan_tcversions TPTCV 
        JOIN tcversions TCV 
        ON TCV.id = TPTCV.tcversion_id 
            JOIN nodes_hierarchy NH_TCV 
            ON NH_TCV.id = TPTCV.
            tcversion_id 
                JOIN nodes_hierarchy 
                NH_TCASE 
                ON NH_TCASE.id = NH_TCV.
                parent_id 
                    LEFT OUTER JOIN 
                    platforms PLAT 
                    ON PLAT.id = TPTCV.
                    platform_id 
                        JOIN (  SELECT
                                    EE.
                                    tcversion_id
                                    ,
                                    EE.
                                    testplan_id
                                    ,
                                    EE.
                                    platform_id
                                    ,
                                    EE.
                                    build_id
                                    ,
                                    MAX(
                                    EE.
                                    id) 
                                    AS 
                                    id 
                                FROM
                                    executions 
                                    EE 
                                WHERE
                                    EE.
                                    testplan_id 
                                    = 15 
                                GROUP BY
                                    EE.
                                    tcversion_id
                                    ,
                                    EE.
                                    testplan_id
                                    ,
                                    EE.
                                    platform_id
                                    ,
                                    EE.
                                    build_id 
                        ) AS LEX 
                        ON LEX.
                        testplan_id = 
                        TPTCV.
                        testplan_id AND
                        LEX.platform_id 
                        = TPTCV.
                        platform_id AND
                        LEX.tcversion_id 
                        = TPTCV.
                        tcversion_id AND
                        LEX.testplan_id 
                        = 15 
                            JOIN 
                            executions E 
                            ON E.
                            tcversion_id 
                            = TPTCV.
                            tcversion_id 
                            AND
                            E.
                            testplan_id 
                            = TPTCV.
                            testplan_id 
                            AND
                            E.
                            platform_id 
                            = TPTCV.
                            platform_id 
WHERE
    TPTCV.testplan_id =15 AND
    TCV.execution_type IN (2 ) AND
    E.status IN ('f') 
UNION
    
    /* Class:testplan - Method: getLinkedTCVersionsSQL sqlUnion - not run */ 
SELECT
    NH_TCASE.name                    AS 
    tcase_name,
    NH_TCASE.id                      AS 
    tcase_id,
    NH_TCASE.id                       AS 
    tc_id,
    TPTCV.tcversion_id,
    TCV.version,
    TCV.tc_external_id               AS 
    external_id,
    TCV.execution_type,
    TPTCV.id                         AS 
    feature_id,
    TPTCV.platform_id,
    PLAT.name                         AS 
    platform_name,
    TPTCV.node_order                  AS 
    execution_order,
    COALESCE(E.status,'n')           AS 
    exec_status,
    CONCAT('TT-',TCV.tc_external_id) AS 
    full_external_id 
FROM
    testplan_tcversions TPTCV 
        JOIN tcversions TCV 
        ON TCV.id = TPTCV.tcversion_id 
            JOIN nodes_hierarchy NH_TCV 
            ON NH_TCV.id = TPTCV.
            tcversion_id 
                JOIN nodes_hierarchy 
                NH_TCASE 
                ON NH_TCASE.id = NH_TCV.
                parent_id 
                    LEFT OUTER JOIN 
                    platforms PLAT 
                    ON PLAT.id = TPTCV.
                    platform_id 
                    /* Get REALLY NOT RUN => BOTH LE.id AND E.id ON LEFT OUTER see WHERE  */ 
                        LEFT OUTER JOIN 
                        (   SELECT
                                EE.
                                tcversion_id
                                ,
                                EE.
                                testplan_id
                                ,
                                EE.
                                platform_id
                                ,
                                EE.
                                build_id
                                ,
                                MAX(EE.
                                id) AS 
                                id 
                            FROM
                                executions 
                                EE 
                            WHERE
                                EE.
                                testplan_id 
                                = 15 
                            GROUP BY
                                EE.
                                tcversion_id
                                ,
                                EE.
                                testplan_id
                                ,
                                EE.
                                platform_id
                                ,
                                EE.
                                build_id 
                        ) AS LEX 
                        ON LEX.
                        testplan_id = 
                        TPTCV.
                        testplan_id AND
                        LEX.platform_id 
                        = TPTCV.
                        platform_id AND
                        LEX.tcversion_id 
                        = TPTCV.
                        tcversion_id AND
                        LEX.testplan_id 
                        = 15 
                            LEFT OUTER 
                            JOIN 
                            executions E 
                            ON E.
                            tcversion_id 
                            = TPTCV.
                            tcversion_id 
                            AND
                            E.
                            testplan_id 
                            = TPTCV.
                            testplan_id 
                            AND
                            E.
                            platform_id 
                            = TPTCV.
                            platform_id 
WHERE
    TPTCV.testplan_id =15 AND
    TCV.execution_type IN (2 ) AND
    E.status IN ('f')