1_query.sql

SELECT 
        `wp`.`id` AS `wpID`,
        `recipient`.`id` AS `recipientid`,
        `recipient`.`recipientTypeID` AS `recipientTypeID`,
        `recipient`.`sortorder` AS `sortorder`,
        (CASE
            WHEN
                ((`cto`.`orgname` IS NOT NULL)
                    AND (`recipient`.`recipientTypeID` = 4))
            THEN
                1
            WHEN
                ((`cto`.`orgname` IS NOT NULL)
                    AND (`recipient`.`recipientTypeID` = 7))
            THEN
                3
            ELSE 2
        END) AS `recipientTypeSort`,
        (CASE
            WHEN (`cto`.`orgname` IS NOT NULL) THEN CONVERT( CAST(`recipient`.`granteeid` AS CHAR CHARSET LATIN1) USING UTF8)
            ELSE `ctc`.`coc_no`
        END) AS `granteeid`,
        (CASE
            WHEN (`cto`.`orgname` IS NOT NULL) THEN `cto`.`grantee_state`
            ELSE `ctc`.`state_id`
        END) AS `granteestate`,
        (CASE
            WHEN (`cto`.`orgname` IS NOT NULL) THEN `cto`.`orgname`
            ELSE CONCAT(`ctc`.`coc_no`, ' - ', `ctc`.`coc_name`)
        END) AS `granteename`,
        `fo`.`orgname` AS `fieldOffice`,
        (CASE
            WHEN
                (`cto`.`orgname` IS NOT NULL)
            THEN
                CONCAT(IFNULL(`cto`.`orgname`, ''),
                        ' - ',
                        IFNULL(`fo`.`orgname`, ''))
            WHEN
                (`ctc`.`coc_no` IS NOT NULL)
            THEN
                CONCAT(IFNULL(CONCAT(`ctc`.`coc_no`, ' - ', `ctc`.`coc_name`),
                                ''),
                        ' - ',
                        IFNULL(`fo`.`orgname`, ''))
            ELSE NULL
        END) AS `granteeFieldOffice`
    FROM
        ((((((`tap_tbl_workplan` `wp`
        LEFT JOIN `tap_asc_oncallwp_assignment` `oncall` ON (((`oncall`.`workplanid` = `wp`.`id`)
            AND (`oncall`.`association_completed` = 1))))
        LEFT JOIN `tap_tbl_assignment` `assign` ON (((`assign`.`id` = `wp`.`assignmentid`)
            OR (`assign`.`id` = `oncall`.`assignmentid`))))
        LEFT JOIN `tap_tbl_recipient` `recipient` ON (((`recipient`.`requestid` = `assign`.`requestid`)
            OR (`recipient`.`workplanid` = `wp`.`id`))))
        LEFT JOIN `c_torganizations` `fo` ON ((`fo`.`orgid` = `recipient`.`fieldofficeid`)))
        LEFT JOIN `c_torganizations` `cto` ON ((`cto`.`orgid` = `recipient`.`granteeid`)))
        LEFT JOIN `c_tcoc` `ctc` ON ((`ctc`.`coc_no` = `recipient`.`coc_no`)))
    ORDER BY 
    `wp`.`id` ,
    
    (CASE
        WHEN
            ((`cto`.`orgname` IS NOT NULL)
                AND (`recipient`.`recipientTypeID` = 4))
        THEN
            1
        WHEN
            ((`cto`.`orgname` IS NOT NULL)
                AND (`recipient`.`recipientTypeID` = 7))
        THEN
            3
        ELSE 2
    END) ,
    
    `recipient`.`recipientTypeID` , `recipient`.`sortorder` , `recipient`.`id`
    

2_order_by.txt

+------+-------------+-----------+--------+----------------------------------------------------------+---------------------------------+---------+-------------------------------------------+------+--------------------------------------------------+
| id   | select_type | table     | type   | possible_keys                                            | key                             | key_len | ref                                       | rows | Extra                                            |
+------+-------------+-----------+--------+----------------------------------------------------------+---------------------------------+---------+-------------------------------------------+------+--------------------------------------------------+
|    1 | SIMPLE      | wp        | index  | NULL                                                     | fk_tap_tbl_workplan1            | 5       | NULL                                      | 2414 | Using index; Using temporary; Using filesort     |
|    1 | SIMPLE      | oncall    | ref    | fk_tap_asc_oncallwp_assignment2                          | fk_tap_asc_oncallwp_assignment2 | 4       | hudexchange_test2.wp.id                   |    3 | Using where                                      |
|    1 | SIMPLE      | assign    | ALL    | PRIMARY                                                  | NULL                            | NULL    | NULL                                      |  537 | Range checked for each record (index map: 0x1)   |
|    1 | SIMPLE      | recipient | ALL    | fk_tap_tbl_recipient1,fk_tap_tbl_recipient5,JAMIE,jamie2 | NULL                            | NULL    | NULL                                      | 5890 | Range checked for each record (index map: 0x2A2) |
|    1 | SIMPLE      | fo        | eq_ref | PRIMARY                                                  | PRIMARY                         | 4       | hudexchange_test2.recipient.fieldofficeid |    1 | Using where                                      |
|    1 | SIMPLE      | cto       | eq_ref | PRIMARY                                                  | PRIMARY                         | 4       | hudexchange_test2.recipient.granteeid     |    1 | Using where                                      |
|    1 | SIMPLE      | ctc       | eq_ref | PRIMARY                                                  | PRIMARY                         | 30      | hudexchange_test2.recipient.coc_no        |    1 | Using where                                      |
+------+-------------+-----------+--------+----------------------------------------------------------+---------------------------------+---------+-------------------------------------------+------+--------------------------------------------------+

3_no_order_by.txt

+------+-------------+-----------+--------+----------------------------------------------------------+---------------------------------+---------+-------------------------------------------+------+--------------------------------------------------+
| id   | select_type | table     | type   | possible_keys                                            | key                             | key_len | ref                                       | rows | Extra                                            |
+------+-------------+-----------+--------+----------------------------------------------------------+---------------------------------+---------+-------------------------------------------+------+--------------------------------------------------+
|    1 | SIMPLE      | wp        | index  | NULL                                                     | fk_tap_tbl_workplan1            | 5       | NULL                                      | 2414 | Using index                                      |
|    1 | SIMPLE      | oncall    | ref    | fk_tap_asc_oncallwp_assignment2                          | fk_tap_asc_oncallwp_assignment2 | 4       | hudexchange_test2.wp.id                   |    3 | Using where                                      |
|    1 | SIMPLE      | assign    | ALL    | PRIMARY                                                  | NULL                            | NULL    | NULL                                      |  537 | Range checked for each record (index map: 0x1)   |
|    1 | SIMPLE      | recipient | ALL    | fk_tap_tbl_recipient1,fk_tap_tbl_recipient5,JAMIE,jamie2 | NULL                            | NULL    | NULL                                      | 5890 | Range checked for each record (index map: 0x2A2) |
|    1 | SIMPLE      | fo        | eq_ref | PRIMARY                                                  | PRIMARY                         | 4       | hudexchange_test2.recipient.fieldofficeid |    1 | Using where                                      |
|    1 | SIMPLE      | cto       | eq_ref | PRIMARY                                                  | PRIMARY                         | 4       | hudexchange_test2.recipient.granteeid     |    1 | Using where                                      |
|    1 | SIMPLE      | ctc       | eq_ref | PRIMARY                                                  | PRIMARY                         | 30      | hudexchange_test2.recipient.coc_no        |    1 | Using where                                      |
+------+-------------+-----------+--------+----------------------------------------------------------+---------------------------------+---------+-------------------------------------------+------+--------------------------------------------------+