nestedTbls for user680522

CREATE TABLE myTempTable AS
SELECT DISTINCT CCSO, arrmap, relatedOrder
FROM (

    /** layer 7 **/
    /* all orders with these items */
    SELECT DISTINCT outerlayer.CCSO, outerlayer.ARRMAP, siblingOrders.CCSO AS relatedOrder
    FROM (
        /* all items on related order */
        SELECT DISTINCT innerlayer.CCSO, siblingItems.ARRMAP, siblingItems.CCSO AS relatedOrder
        FROM (

            /** layer 6 **/
            /* all orders with these items */
            SELECT DISTINCT outerlayer.CCSO, outerlayer.ARRMAP, siblingOrders.CCSO AS relatedOrder
            FROM (
                /* all items on related order */
                SELECT DISTINCT innerlayer.CCSO, siblingItems.ARRMAP, siblingItems.CCSO AS relatedOrder
                FROM (

                    /** layer 5 **/
                    /* all orders with these items */
                    SELECT DISTINCT outerlayer.CCSO, outerlayer.ARRMAP, siblingOrders.CCSO AS relatedOrder
                    FROM (
                        /* all items on related order */
                        SELECT DISTINCT innerlayer.CCSO, siblingItems.ARRMAP, siblingItems.CCSO AS relatedOrder
                        FROM (

                            /** layer 4 **/
                            /* all orders with these items */
                            SELECT DISTINCT outerlayer.CCSO, outerlayer.ARRMAP, siblingOrders.CCSO AS relatedOrder
                            FROM (
                                /* all items on related order */
                                SELECT DISTINCT innerlayer.CCSO, siblingItems.ARRMAP, siblingItems.CCSO AS relatedOrder
                                FROM (

                                    /** layer 3 **/
                                    /* all orders with these items */
                                    SELECT DISTINCT outerlayer.CCSO, outerlayer.ARRMAP, siblingOrders.CCSO AS relatedOrder
                                    FROM (
                                        /* all items on related order */
                                        SELECT DISTINCT innerlayer.CCSO, siblingItems.ARRMAP, siblingItems.CCSO AS relatedOrder
                                        FROM (

                                            /** layer 2 **/
                                            /* all orders with these items */
                                            SELECT DISTINCT outerlayer.CCSO, outerlayer.ARRMAP, siblingOrders.CCSO AS relatedOrder
                                            FROM (
                                                /* all items on related order */
                                                SELECT DISTINCT innerlayer.CCSO, siblingItems.ARRMAP, siblingItems.CCSO AS relatedOrder
                                                FROM (
                                                        /** LAYER 1 **/

                                                        /* all orders containing same item */
                                                        SELECT DISTINCT layer0.CCSO, layer0.ARRMAP, siblingOrders.CCSO AS relatedOrder
                                                        FROM myTable layer0
                                                        left join myTable siblingOrders  ON  (layer0.ARRMAP = siblingOrders.ARRMAP AND layer0.CCSO <> siblingOrders.CCSO)

                                                        UNION ALL

                                                        /* all items on same order */
                                                        SELECT DISTINCT layer0.CCSO, siblingItems.ARRMAP, siblingItems.CCSO AS relatedOrder
                                                        FROM myTable layer0
                                                        inner join myTable siblingItems  ON  (layer0.ARRMAP <> siblingItems.ARRMAP AND layer0.CCSO = siblingItems.CCSO)

                                                        /** END LAYER 1 **/

                                                ) innerlayer
                                                inner join myTable siblingItems  ON  (innerlayer.ARRMAP <> siblingItems.ARRMAP AND innerlayer.relatedOrder = siblingItems.CCSO)
                                            ) outerlayer
                                            left join myTable siblingOrders  ON  (outerlayer.ARRMAP = siblingOrders.ARRMAP AND outerlayer.CCSO <> siblingOrders.CCSO)
                                            /** end layer 2 **/

                                        ) innerlayer
                                        inner join myTable siblingItems  ON  (innerlayer.ARRMAP <> siblingItems.ARRMAP AND innerlayer.relatedOrder = siblingItems.CCSO)
                                    ) outerlayer
                                    left join myTable siblingOrders  ON  (outerlayer.ARRMAP = siblingOrders.ARRMAP AND outerlayer.CCSO <> siblingOrders.CCSO)
                                    /** end layer 3 **/

                                ) innerlayer
                                inner join myTable siblingItems  ON  (innerlayer.ARRMAP <> siblingItems.ARRMAP AND innerlayer.relatedOrder = siblingItems.CCSO)
                            ) outerlayer
                            left join myTable siblingOrders  ON  (outerlayer.ARRMAP = siblingOrders.ARRMAP AND outerlayer.CCSO <> siblingOrders.CCSO)
                            /** end layer 4 **/

                        ) innerlayer
                        inner join myTable siblingItems  ON  (innerlayer.ARRMAP <> siblingItems.ARRMAP AND innerlayer.relatedOrder = siblingItems.CCSO)
                    ) outerlayer
                    left join myTable siblingOrders  ON  (outerlayer.ARRMAP = siblingOrders.ARRMAP AND outerlayer.CCSO <> siblingOrders.CCSO)
                    /** end layer 5 **/

                ) innerlayer
                inner join myTable siblingItems  ON  (innerlayer.ARRMAP <> siblingItems.ARRMAP AND innerlayer.relatedOrder = siblingItems.CCSO)
            ) outerlayer
            left join myTable siblingOrders  ON  (outerlayer.ARRMAP = siblingOrders.ARRMAP AND outerlayer.CCSO <> siblingOrders.CCSO)
            /** end layer 6 **/

        ) innerlayer
        inner join myTable siblingItems  ON  (innerlayer.ARRMAP <> siblingItems.ARRMAP AND innerlayer.relatedOrder = siblingItems.CCSO)
    ) outerlayer
    left join myTable siblingOrders  ON  (outerlayer.ARRMAP = siblingOrders.ARRMAP AND outerlayer.CCSO <> siblingOrders.CCSO)
    /** end layer 7 **/

) nestedTbls
ORDER BY 1, 2, 3;
--244s