SELECT
      'match' AS mailoutType,
      '' AS mailoutID,
      `mailout_pending`.`mailoutCreatedID`,
      'applicant' AS uniqueType,
      `mailout_pending`.`applicantID` AS uniqueID,
      GROUP_CONCAT(`mailout_pending`.`profileID`) AS profileID,
      `mailout_pending`.`mailoutFormat`,
      `mailout_pending`.`mailoutFrom`,
      `mailout_pending`.`mailoutFromOffice`,
      `mailout_pending`.`mailoutOwner`,
      `mailout_pending`.`dateCreated`,
      `mailout_pending`.`recordCreatedBy`,
      `mailout_pending`.`followUpRequired`,
      `mailout_created`.`mailoutCustomMessage`,
      `applicant_view`.`contactID`,
      `applicant_view`.`name`,
      `applicant_view`.`emailAddress`,
      `applicant_view`.`applicantType`,
      `mailout_pending`.`recordCreatedBy`
      FROM 
      `mailout_pending`
      INNER JOIN `mailout_created` ON `mailout_created`.`mailoutCreatedID`=`mailout_pending`.`mailoutCreatedID`
      INNER JOIN `applicant_view` ON `mailout_pending`.`applicantID`=`applicant_view`.`applicantID`
      
      WHERE
      `mailout_pending`.`mailoutMethod`='email'
     AND     
      (
       `mailout_pending`.`deliveryOption`='now'
       OR
       (
        `mailout_pending`.`deliveryOption`='endOfToday' AND 
        (
         (DATE_FORMAT(`mailout_pending`.`dateCreated`,'%Y-%m-%d')<CURDATE())
          OR
         (DATE_FORMAT(`mailout_pending`.`dateCreated`,'%Y-%m-%d')=CURDATE() AND CURTIME() > '19:00:00')
        )
       )
       OR
       (
        `mailout_pending`.`deliveryOption`='endOfWeek' AND 
        (
         (DATE_ADD(`mailout_pending`.`dateCreated`, INTERVAL mod(6-DAYOFWEEK(`mailout_pending`.`dateCreated`)+7,7) DAY)<=CURDATE()) /* SEND ON THE FRIDAY OF DATE CREATED */
        )
       )
      )
      AND `mailout_pending`.instantSendID IS NULL
      GROUP BY `mailoutCreatedID`, `mailout_pending`.`applicantID`
      
      UNION ALL
      
      SELECT
      'match' AS mailoutType,
      '' AS mailoutID,
      `mailout_pending`.`mailoutCreatedID`,
      'applicant' AS uniqueType,
      `mailout_pending`.`applicantID` AS uniqueID,
      GROUP_CONCAT(`mailout_pending`.`profileID`) AS profileID,
      `mailout_pending`.`mailoutFormat`,
      `mailout_pending`.`mailoutFrom`,
      `mailout_pending`.`mailoutFromOffice`,
      `mailout_pending`.`mailoutOwner`,
      `mailout_pending`.`dateCreated`,
      `mailout_pending`.`recordCreatedBy`,
      `mailout_pending`.`followUpRequired`,
      '' AS mailoutCustomMessage,
      `applicant_view`.`contactID`,
      `applicant_view`.`name`,
      `applicant_view`.`emailAddress`,
      `applicant_view`.`applicantType`,
      `mailout_pending`.`recordCreatedBy`
      FROM 
      `mailout_pending`
      INNER JOIN `mailout_created` ON `mailout_created`.`mailoutCreatedID`=`mailout_pending`.`mailoutCreatedID`
      INNER JOIN `applicant_view` ON `mailout_pending`.`applicantID`=`applicant_view`.`applicantID`
      
      WHERE
      `mailout_pending`.`mailoutMethod`='email'
     AND     
      (
       `mailout_pending`.`deliveryOption`='contactPreference'
       AND
       (
        DATE_ADD(`applicant_view`.`mailoutLastDate`, INTERVAL `applicant_view`.`mailoutFrequency` HOUR)<=NOW()
         OR
        `applicant_view`.`mailoutLastDate`='0000-00-00 00:00:00'
         OR
        `applicant_view`.`mailoutFrequency`='0'
       )
      )
      AND `mailout_pending`.instantSendID is null
      GROUP BY `mailoutCreatedID`, `mailout_pending`.`applicantID`
      
      UNION ALL
      
      SELECT
      'adhoc' AS mailoutType,
      `adhoc`.`mailoutID`,
      '' AS mailoutCreatedID,
      `adhoc_recipient_email`.`uniqueType`,
      `adhoc_recipient_email`.`uniqueID`,
      '',
      '',
      `adhoc`.`mailoutFrom`,
      `adhoc`.`mailoutFromOffice`,
      `adhoc`.`recordCreatedBy` AS mailoutOwner,
      `adhoc`.`dateCreated`,
      `adhoc`.`recordCreatedBy`,
      '',
      '',
      `adhoc_recipient_email`.`uniqueID` AS contactID,
      `adhoc_recipient_email`.`emailTo` AS name,
      `adhoc_recipient_email`.`emailToAddress` AS emailAddress,
      '',
      `adhoc`.`recordCreatedBy`
    FROM
      `adhoc`
    INNER JOIN `adhoc_recipient_email` ON `adhoc_recipient_email`.`mailoutID`=`adhoc`.`mailoutID`
    INNER JOIN `adhoc_email` ON `adhoc_email`.`mailoutID`=`adhoc`.`mailoutID`
    INNER JOIN `contact_view` ON `contact_view`.`contactID`=`adhoc_recipient_email`.`uniqueID`
    WHERE
     (`adhoc_recipient_email`.`uniqueType`='contact' OR `adhoc_recipient_email`.`uniqueType`='contractor')
    AND
     `adhoc_recipient_email`.`status`=''
    AND
     (
       `adhoc`.`deliveryOption`='now'
      OR
       (`adhoc`.`deliveryOption`='endOfToday' AND 
        (
         (DATE_FORMAT(`adhoc`.`dateCreated`,'%Y-%m-%d')<CURDATE()) /* IF DAY HAS PASSED */
          OR
         (DATE_FORMAT(`adhoc`.`dateCreated`,'%Y-%m-%d')=CURDATE() AND CURTIME() > '19:00:00') /* IF LATER THAN 18:00:00 ON THE DAY THE MAILOUT WAS CREATED */
        )
       )
      OR
       (`adhoc`.`deliveryOption`='endOfWeek' AND 
        (
         (DATE_ADD(`adhoc`.`dateCreated`, INTERVAL mod(6-DAYOFWEEK(`adhoc`.`dateCreated`)+7,7) DAY)<=CURDATE())  /* SEND ON THE FRIDAY OF DATE CREATED */
        )
       )
     )
    
    UNION ALL
      
      SELECT
      'adhoc' AS mailoutType,
      `adhoc`.`mailoutID`,
      '' AS mailoutCreatedID,
      `adhoc_recipient_email`.`uniqueType`,
      `adhoc_recipient_email`.`uniqueID`,
      '',
      '',
      `adhoc`.`mailoutFrom`,
      `adhoc`.`mailoutFromOffice`,
      `adhoc`.`recordCreatedBy` AS mailoutOwner,
      `adhoc`.`dateCreated`,
      `adhoc`.`recordCreatedBy`,
      '',
      '',
      `applicant_view`.`contactID`,
      `adhoc_recipient_email`.`emailTo` AS name,
      `adhoc_recipient_email`.`emailToAddress` AS emailAddress,
      '',
      `adhoc`.`recordCreatedBy`
    FROM
      `adhoc`
    INNER JOIN `adhoc_recipient_email` ON `adhoc_recipient_email`.`mailoutID`=`adhoc`.`mailoutID`
    INNER JOIN `adhoc_email` ON `adhoc_email`.`mailoutID`=`adhoc`.`mailoutID`
    INNER JOIN `applicant_view` ON `applicant_view`.`applicantID`=`adhoc_recipient_email`.`uniqueID`
    WHERE
     `adhoc_recipient_email`.`uniqueType`='applicant'
    AND
     `adhoc_recipient_email`.`status`=''
    AND
     (
       `adhoc`.`deliveryOption`='now'
      OR
       (`adhoc`.`deliveryOption`='endOfToday' AND 
        (
         (DATE_FORMAT(`adhoc`.`dateCreated`,'%Y-%m-%d')<CURDATE()) /* IF DAY HAS PASSED */
          OR
         (DATE_FORMAT(`adhoc`.`dateCreated`,'%Y-%m-%d')=CURDATE() AND CURTIME() > '19:00:00') /* IF LATER THAN 18:00:00 ON THE DAY THE MAILOUT WAS CREATED */
        )
       )
      OR
       (`adhoc`.`deliveryOption`='endOfWeek' AND 
        (
         (DATE_ADD(`adhoc`.`dateCreated`, INTERVAL mod(6-DAYOFWEEK(`adhoc`.`dateCreated`)+7,7) DAY)<=CURDATE())  /* SEND ON THE FRIDAY OF DATE CREATED */
        )
       )
     )
     
     UNION ALL
      
     SELECT
      'adhoc' AS mailoutType,
      `adhoc`.`mailoutID`,
      '' AS mailoutCreatedID,
      `adhoc_recipient_email`.`uniqueType`,
      `adhoc_recipient_email`.`uniqueID`,
      '',
      '',
      `adhoc`.`mailoutFrom`,
      `adhoc`.`mailoutFromOffice`,
      `adhoc`.`recordCreatedBy` AS mailoutOwner,
      `adhoc`.`dateCreated`,
      `adhoc`.`recordCreatedBy`,
      '',
      '',
      `contact_view`.`contactID`,
      `adhoc_recipient_email`.`emailTo` AS name,
      `adhoc_recipient_email`.`emailToAddress` AS emailAddress,
      '',
      `adhoc`.`recordCreatedBy`
    FROM
      `adhoc`
    INNER JOIN `adhoc_recipient_email` ON `adhoc_recipient_email`.`mailoutID`=`adhoc`.`mailoutID`
    INNER JOIN `adhoc_email` ON `adhoc_email`.`mailoutID`=`adhoc`.`mailoutID`
    INNER JOIN `owner` ON `owner`.`ownerID`=`adhoc_recipient_email`.`uniqueID` 
    INNER JOIN `contact_view` ON `contact_view`.`contactID`=`owner`.`contactID`
    WHERE
     `adhoc_recipient_email`.`uniqueType`='owner'
    AND
     `adhoc_recipient_email`.`status`=''
    AND
     (
       `adhoc`.`deliveryOption`='now'
      OR
       (`adhoc`.`deliveryOption`='endOfToday' AND 
        (
         (DATE_FORMAT(`adhoc`.`dateCreated`,'%Y-%m-%d')<CURDATE()) /* IF DAY HAS PASSED */
          OR
         (DATE_FORMAT(`adhoc`.`dateCreated`,'%Y-%m-%d')=CURDATE() AND CURTIME() > '19:00:00') /* IF LATER THAN 18:00:00 ON THE DAY THE MAILOUT WAS CREATED */
        )
      )
      OR
       (`adhoc`.`deliveryOption`='endOfWeek' AND 
        (
         (DATE_ADD(`adhoc`.`dateCreated`, INTERVAL mod(6-DAYOFWEEK(`adhoc`.`dateCreated`)+7,7) DAY)<=CURDATE())  /* SEND ON THE FRIDAY OF DATE CREATED */
        )
       )
     )
    
    UNION ALL
      
      SELECT
      'adhoc' AS mailoutType,
      `adhoc`.`mailoutID`,
      '' AS mailoutCreatedID,
      `adhoc_recipient_email`.`uniqueType`,
      `adhoc_recipient_email`.`uniqueID`,
      '',
      '',
      `adhoc`.`mailoutFrom`,
      `adhoc`.`mailoutFromOffice`,
      `adhoc`.`recordCreatedBy` AS mailoutOwner,
      `adhoc`.`dateCreated`,
      `adhoc`.`recordCreatedBy`,
      '',
      '',
      `contact_view`.`contactID`,
      `adhoc_recipient_email`.`emailTo` AS name,
      `adhoc_recipient_email`.`emailToAddress` AS emailAddress,
      '',
      `adhoc`.`recordCreatedBy`
    FROM
      `adhoc`
    INNER JOIN `adhoc_recipient_email` ON `adhoc_recipient_email`.`mailoutID`=`adhoc`.`mailoutID`
    INNER JOIN `adhoc_email` ON `adhoc_email`.`mailoutID`=`adhoc`.`mailoutID`
    INNER JOIN `owner_potential` ON `owner_potential`.`potentialOwnerID`=`adhoc_recipient_email`.`uniqueID` 
    INNER JOIN `contact_view` ON `contact_view`.`contactID`=`owner_potential`.`contactID`
    WHERE
     `adhoc_recipient_email`.`uniqueType`='potentialOwner'
    AND
     `adhoc_recipient_email`.`status`=''
    AND
     (
       `adhoc`.`deliveryOption`='now'
      OR
       (`adhoc`.`deliveryOption`='endOfToday' AND 
        (
         (DATE_FORMAT(`adhoc`.`dateCreated`,'%Y-%m-%d')<CURDATE()) /* IF DAY HAS PASSED */
          OR
         (DATE_FORMAT(`adhoc`.`dateCreated`,'%Y-%m-%d')=CURDATE() AND CURTIME() > '19:00:00') /* IF LATER THAN 18:00:00 ON THE DAY THE MAILOUT WAS CREATED */
        )
       )
      OR
       (`adhoc`.`deliveryOption`='endOfWeek' AND 
        (
         (DATE_ADD(`adhoc`.`dateCreated`, INTERVAL mod(6-DAYOFWEEK(`adhoc`.`dateCreated`)+7,7) DAY)<=CURDATE())  /* SEND ON THE FRIDAY OF DATE CREATED */
        )
       )
     )
    
    UNION ALL
      
    SELECT
      'adhoc' AS mailoutType,
      `adhoc`.`mailoutID`,
      '' AS mailoutCreatedID,
      `adhoc_recipient_email`.`uniqueType`,
      `adhoc_recipient_email`.`uniqueID`,
      '',
      '',
      `adhoc`.`mailoutFrom`,
      `adhoc`.`mailoutFromOffice`,
      `adhoc`.`recordCreatedBy` AS mailoutOwner,
      `adhoc`.`dateCreated`,
      `adhoc`.`recordCreatedBy`,
      '',
      '',
      '0' AS contactID,
      `adhoc_recipient_email`.`emailTo` AS name,
      `adhoc_recipient_email`.`emailToAddress` AS emailAddress,
      '',
      `adhoc`.`recordCreatedBy`
    FROM
      `adhoc`
    INNER JOIN `adhoc_recipient_email` ON `adhoc_recipient_email`.`mailoutID`=`adhoc`.`mailoutID`
    INNER JOIN `adhoc_email` ON `adhoc_email`.`mailoutID`=`adhoc`.`mailoutID`
    WHERE
     `adhoc_recipient_email`.`uniqueType`='custom'
    AND
     `adhoc_recipient_email`.`status`=''
    AND
     (
       `adhoc`.`deliveryOption`='now'
      OR
       (`adhoc`.`deliveryOption`='endOfToday' AND 
        (
         (DATE_FORMAT(`adhoc`.`dateCreated`,'%Y-%m-%d')<CURDATE()) /* IF DAY HAS PASSED */
          OR
         (DATE_FORMAT(`adhoc`.`dateCreated`,'%Y-%m-%d')=CURDATE() AND CURTIME() > '19:00:00') /* IF LATER THAN 18:00:00 ON THE DAY THE MAILOUT WAS CREATED */
        )
       )
     )
      
      ORDER BY mailoutType,contactID,dateCreated;

+------+--------------+-----------------------+-------------+----------------------------------------------------+------------------------------+---------+-----------------------------------------------+------+----------+---------------------------------------------------------------------------------------------+
| id   | select_type  | table                 | type        | possible_keys                                      | key                          | key_len | ref                                           | rows | filtered | Extra                                                                                       |
+------+--------------+-----------------------+-------------+----------------------------------------------------+------------------------------+---------+-----------------------------------------------+------+----------+---------------------------------------------------------------------------------------------+
|    1 | PRIMARY      | mailout_pending       | ref         | PRIMARY,mailoutMethod,deliveryOption,instantSendID | instantSendID                | 23      | const                                         |    1 |   100.00 | Using index condition; Using where; Using temporary; Using filesort                         |
|    1 | PRIMARY      | mailout_created       | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.mailout_pending.mailoutCreatedID |    1 |   100.00 | Using join buffer (flat, BKAH join); Key-ordered scan                                       |
|    1 | PRIMARY      | applicant             | eq_ref      | PRIMARY,contactID                                  | PRIMARY                      | 4       | jb_hymanhill.mailout_pending.applicantID      |    1 |   100.00 | Using join buffer (incremental, BKAH join); Key-ordered scan                                |
|    1 | PRIMARY      | contact               | eq_ref      | PRIMARY,contactID,contactID_2,primaryPartyID       | PRIMARY                      | 4       | jb_hymanhill.applicant.contactID              |    1 |   100.00 | Using where; Using join buffer (incremental, BKAH join); Key-ordered scan                   |
|    1 | PRIMARY      | individual            | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.contact.primaryPartyID           |    1 |   100.00 | Using where; Using join buffer (incremental, BKAH join); Key-ordered scan                   |
|    1 | PRIMARY      | company               | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.contact.primaryPartyID           |    1 |   100.00 | Using where; Using join buffer (incremental, BKAH join); Key-ordered scan                   |
|    1 | PRIMARY      | prospect              | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.contact.primaryPartyID           |    1 |   100.00 | Using where; Using join buffer (incremental, BKAH join); Key-ordered scan                   |
|    2 | UNION        | mailout_pending       | index_merge | PRIMARY,mailoutMethod,deliveryOption,instantSendID | instantSendID,deliveryOption | 23,32   | NULL                                          |    1 |     8.33 | Using intersect(instantSendID,deliveryOption); Using where; Using temporary; Using filesort |
|    2 | UNION        | mailout_created       | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.mailout_pending.mailoutCreatedID |    1 |   100.00 | Using index                                                                                 |
|    2 | UNION        | applicant             | eq_ref      | PRIMARY,contactID                                  | PRIMARY                      | 4       | jb_hymanhill.mailout_pending.applicantID      |    1 |   100.00 | Using where; Using join buffer (flat, BKAH join); Key-ordered scan                          |
|    2 | UNION        | contact               | eq_ref      | PRIMARY,contactID,contactID_2,primaryPartyID       | PRIMARY                      | 4       | jb_hymanhill.applicant.contactID              |    1 |   100.00 | Using where; Using join buffer (incremental, BKAH join); Key-ordered scan                   |
|    2 | UNION        | individual            | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.contact.primaryPartyID           |    1 |   100.00 | Using where; Using join buffer (incremental, BKAH join); Key-ordered scan                   |
|    2 | UNION        | company               | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.contact.primaryPartyID           |    1 |   100.00 | Using where; Using join buffer (incremental, BKAH join); Key-ordered scan                   |
|    2 | UNION        | prospect              | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.contact.primaryPartyID           |    1 |   100.00 | Using where; Using join buffer (incremental, BKAH join); Key-ordered scan                   |
|    3 | UNION        | adhoc_recipient_email | ref         | status,mailoutID,uniqueID                          | status                       | 17      | const                                         |    1 |   100.00 | Using index condition; Using where                                                          |
|    3 | UNION        | adhoc_email           | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.adhoc_recipient_email.mailoutID  |    1 |   100.00 | Using index                                                                                 |
|    3 | UNION        | adhoc                 | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.adhoc_recipient_email.mailoutID  |    1 |    32.47 | Using where; Using join buffer (flat, BKAH join); Key-ordered scan                          |
|    3 | UNION        | contact               | ref         | PRIMARY,contactID,contactID_2,primaryPartyID       | contactID                    | 4       | jb_hymanhill.adhoc_recipient_email.uniqueID   |    1 |   100.00 | Using where; Using index                                                                    |
|    3 | UNION        | individual            | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.contact.primaryPartyID           |    1 |   100.00 | Using where; Using index                                                                    |
|    3 | UNION        | company               | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.contact.primaryPartyID           |    1 |   100.00 | Using where; Using index                                                                    |
|    3 | UNION        | prospect              | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.contact.primaryPartyID           |    1 |   100.00 | Using where; Using index                                                                    |
|    4 | UNION        | adhoc_recipient_email | ref         | status,mailoutID,uniqueID                          | status                       | 17      | const                                         |    1 |   100.00 | Using index condition; Using where                                                          |
|    4 | UNION        | adhoc_email           | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.adhoc_recipient_email.mailoutID  |    1 |   100.00 | Using index                                                                                 |
|    4 | UNION        | adhoc                 | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.adhoc_recipient_email.mailoutID  |    1 |    32.47 | Using where; Using join buffer (flat, BKAH join); Key-ordered scan                          |
|    4 | UNION        | applicant             | eq_ref      | PRIMARY,contactID                                  | PRIMARY                      | 4       | jb_hymanhill.adhoc_recipient_email.uniqueID   |    1 |   100.00 | Using join buffer (incremental, BKAH join); Key-ordered scan                                |
|    4 | UNION        | contact               | ref         | PRIMARY,contactID,contactID_2,primaryPartyID       | contactID                    | 4       | jb_hymanhill.applicant.contactID              |    1 |   100.00 | Using where; Using index                                                                    |
|    4 | UNION        | individual            | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.contact.primaryPartyID           |    1 |   100.00 | Using where; Using index                                                                    |
|    4 | UNION        | company               | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.contact.primaryPartyID           |    1 |   100.00 | Using where; Using index                                                                    |
|    4 | UNION        | prospect              | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.contact.primaryPartyID           |    1 |   100.00 | Using where; Using index                                                                    |
|    5 | UNION        | adhoc_recipient_email | ref         | status,mailoutID,uniqueID                          | status                       | 17      | const                                         |    1 |   100.00 | Using index condition; Using where                                                          |
|    5 | UNION        | adhoc_email           | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.adhoc_recipient_email.mailoutID  |    1 |   100.00 | Using index                                                                                 |
|    5 | UNION        | adhoc                 | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.adhoc_recipient_email.mailoutID  |    1 |    32.47 | Using where; Using join buffer (flat, BKAH join); Key-ordered scan                          |
|    5 | UNION        | owner                 | ref         | PRIMARY,contactID,ownerID                          | PRIMARY                      | 4       | jb_hymanhill.adhoc_recipient_email.uniqueID   |    1 |   100.00 | Using index                                                                                 |
|    5 | UNION        | contact               | ref         | PRIMARY,contactID,contactID_2,primaryPartyID       | contactID                    | 4       | jb_hymanhill.owner.contactID                  |    1 |   100.00 | Using where; Using index                                                                    |
|    5 | UNION        | individual            | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.contact.primaryPartyID           |    1 |   100.00 | Using where; Using index                                                                    |
|    5 | UNION        | company               | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.contact.primaryPartyID           |    1 |   100.00 | Using where; Using index                                                                    |
|    5 | UNION        | prospect              | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.contact.primaryPartyID           |    1 |   100.00 | Using where; Using index                                                                    |
|    6 | UNION        | adhoc_recipient_email | ref         | status,mailoutID,uniqueID                          | status                       | 17      | const                                         |    1 |   100.00 | Using index condition; Using where                                                          |
|    6 | UNION        | owner_potential       | eq_ref      | PRIMARY,contactID                                  | PRIMARY                      | 4       | jb_hymanhill.adhoc_recipient_email.uniqueID   |    1 |   100.00 | Using where; Using join buffer (flat, BKAH join); Key-ordered scan                          |
|    6 | UNION        | adhoc_email           | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.adhoc_recipient_email.mailoutID  |    1 |   100.00 | Using index                                                                                 |
|    6 | UNION        | adhoc                 | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.adhoc_recipient_email.mailoutID  |    1 |    32.47 | Using where; Using join buffer (flat, BKAH join); Key-ordered scan                          |
|    6 | UNION        | contact               | ref         | PRIMARY,contactID,contactID_2,primaryPartyID       | contactID                    | 4       | jb_hymanhill.owner_potential.contactID        |    1 |   100.00 | Using where; Using index                                                                    |
|    6 | UNION        | individual            | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.contact.primaryPartyID           |    1 |   100.00 | Using where; Using index                                                                    |
|    6 | UNION        | company               | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.contact.primaryPartyID           |    1 |   100.00 | Using where; Using index                                                                    |
|    6 | UNION        | prospect              | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.contact.primaryPartyID           |    1 |   100.00 | Using where; Using index                                                                    |
|    7 | UNION        | adhoc_recipient_email | ref         | status,mailoutID                                   | status                       | 17      | const                                         |    1 |   100.00 | Using index condition; Using where                                                          |
|    7 | UNION        | adhoc_email           | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.adhoc_recipient_email.mailoutID  |    1 |   100.00 | Using index                                                                                 |
|    7 | UNION        | adhoc                 | eq_ref      | PRIMARY                                            | PRIMARY                      | 4       | jb_hymanhill.adhoc_recipient_email.mailoutID  |    1 |    32.25 | Using where; Using join buffer (flat, BKAH join); Key-ordered scan                          |
| NULL | UNION RESULT | <union1,2,3,4,5,6,7>  | ALL         | NULL                                               | NULL                         | NULL    | NULL                                          | NULL |     NULL | Using filesort                                                                              |
+------+--------------+-----------------------+-------------+----------------------------------------------------+------------------------------+---------+-----------------------------------------------+------+----------+---------------------------------------------------------------------------------------------+
49 rows in set, 1 warning (0.00 sec)

select 'match' AS `mailoutType`,'' AS `mailoutID`,`jb_hymanhill`.`mailout_pending`.`mailoutCreatedID` AS `mailoutCreatedID`,'applicant' AS `uniqueType`,`jb_hymanhill`.`mailout_pending`.`applicantID` AS `uniqueID`,group_concat(`jb_hymanhill`.`mailout_pending`.`profileID` separator ',') AS `profileID`,`jb_hymanhill`.`mailout_pending`.`mailoutFormat` AS `mailoutFormat`,`jb_hymanhill`.`mailout_pending`.`mailoutFrom` AS `mailoutFrom`,`jb_hymanhill`.`mailout_pending`.`mailoutFromOffice` AS `mailoutFromOffice`,`jb_hymanhill`.`mailout_pending`.`mailoutOwner` AS `mailoutOwner`,`jb_hymanhill`.`mailout_pending`.`dateCreated` AS `dateCreated`,`jb_hymanhill`.`mailout_pending`.`recordCreatedBy` AS `recordCreatedBy`,`jb_hymanhill`.`mailout_pending`.`followUpRequired` AS `followUpRequired`,`jb_hymanhill`.`mailout_created`.`mailoutCustomMessage` AS `mailoutCustomMessage`,`jb_hymanhill`.`contact`.`contactID` AS `contactID`,if((`jb_hymanhill`.`applicant`.`otherAgentsApplicant` <> 1),`jb_hymanhill`.`contact`.`contactViewName`,concat(`jb_hymanhill`.`applicant`.`otherAgentsApplicantName`,_latin1' <br>',`jb_hymanhill`.`contact`.`contactViewName`)) AS `name`,if((`jb_hymanhill`.`contact`.`primaryPartyType` <> _latin1'I'),if((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'C'),`jb_hymanhill`.`company`.`emailAddress`,`jb_hymanhill`.`prospect`.`emailAddress`),`jb_hymanhill`.`individual`.`emailAddress`) AS `emailAddress`,`jb_hymanhill`.`applicant`.`applicantType` AS `applicantType`,`jb_hymanhill`.`mailout_pending`.`recordCreatedBy` AS `recordCreatedBy` from `jb_hymanhill`.`mailout_pending` join `jb7_hymanhill`.`mailout_created` join `jb_hymanhill`.`applicant` join `jb_hymanhill`.`contact` left join `jb_hymanhill`.`individual` on((`jb_hymanhill`.`individual`.`individualID` = `jb_hymanhill`.`contact`.`primaryPartyID`)) left join `jb_hymanhill`.`company` on((`jb_hymanhill`.`company`.`companyID` = `jb_hymanhill`.`contact`.`primaryPartyID`)) left join `jb_hymanhill`.`prospect` on((`jb_hymanhill`.`prospect`.`prospectID` = `jb_hymanhill`.`contact`.`primaryPartyID`)) where ((`jb_hymanhill`.`mailout_pending`.`mailoutMethod` = 'email') and (`jb_hymanhill`.`applicant`.`applicantID` = `jb_hymanhill`.`mailout_pending`.`applicantID`) and (`jb_hymanhill`.`contact`.`contactID` = `jb_hymanhill`.`applicant`.`contactID`) and (`jb_hymanhill`.`mailout_created`.`mailoutCreatedID` = `jb_hymanhill`.`mailout_pending`.`mailoutCreatedID`) and ((`jb_hymanhill`.`mailout_pending`.`deliveryOption` = 'now') or ((`jb_hymanhill`.`mailout_pending`.`deliveryOption` = 'endOfToday') and (date_format(`jb_hymanhill`.`mailout_pending`.`dateCreated`,'%Y-%m-%d') < <cache>(curdate()))) or ((`jb_hymanhill`.`mailout_pending`.`deliveryOption` = 'endOfWeek') and ((`jb_hymanhill`.`mailout_pending`.`dateCreated` + interval (((6 - dayofweek(`jb_hymanhill`.`mailout_pending`.`dateCreated`)) + 7) % 7) day) <= <cache>(curdate())))) and isnull(`jb_hymanhill`.`mailout_pending`.`instantSendID`) and (((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'I') and (`jb_hymanhill`.`individual`.`individualID` is not null)) or ((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'C') and (`jb_hymanhill`.`company`.`companyID` is not null)) or ((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'P') and (`jb_hymanhill`.`prospect`.`prospectID` is not null)))) group by `jb_hymanhill`.`mailout_pending`.`mailoutCreatedID`,`jb_hymanhill`.`mailout_pending`.`applicantID` union all select 'match' AS `mailoutType`,'' AS `mailoutID`,`jb_hymanhill`.`mailout_pending`.`mailoutCreatedID` AS `mailoutCreatedID`,'applicant' AS `uniqueType`,`jb_hymanhill`.`mailout_pending`.`applicantID` AS `uniqueID`,group_concat(`jb_hymanhill`.`mailout_pending`.`profileID` separator ',') AS `profileID`,`jb_hymanhill`.`mailout_pending`.`mailoutFormat` AS `mailoutFormat`,`jb_hymanhill`.`mailout_pending`.`mailoutFrom` AS `mailoutFrom`,`jb_hymanhill`.`mailout_pending`.`mailoutFromOffice` AS `mailoutFromOffice`,`jb_hymanhill`.`mailout_pending`.`mailoutOwner` AS `mailoutOwner`,`jb_hymanhill`.`mailout_pending`.`dateCreated` AS `dateCreated`,`jb_hymanhill`.`mailout_pending`.`recordCreatedBy` AS `recordCreatedBy`,`jb_hymanhill`.`mailout_pending`.`followUpRequired` AS `followUpRequired`,'' AS `mailoutCustomMessage`,`jb_hymanhill`.`contact`.`contactID` AS `contactID`,if((`jb_hymanhill`.`applicant`.`otherAgentsApplicant` <> 1),`jb_hymanhill`.`contact`.`contactViewName`,concat(`jb_hymanhill`.`applicant`.`otherAgentsApplicantName`,_latin1' <br>',`jb_hymanhill`.`contact`.`contactViewName`)) AS `name`,if((`jb_hymanhill`.`contact`.`primaryPartyType` <> _latin1'I'),if((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'C'),`jb_hymanhill`.`company`.`emailAddress`,`jb_hymanhill`.`prospect`.`emailAddress`),`jb_hymanhill`.`individual`.`emailAddress`) AS `emailAddress`,`jb_hymanhill`.`applicant`.`applicantType` AS `applicantType`,`jb_hymanhill`.`mailout_pending`.`recordCreatedBy` AS `recordCreatedBy` from `jb_hymanhill`.`mailout_pending` join `jb_hymanhill`.`mailout_created` join `jb_hymanhill`.`applicant` join `jb_hymanhill`.`contact` left join `jb_hymanhill`.`individual` on((`jb_hymanhill`.`individual`.`individualID` = `jb_hymanhill`.`contact`.`primaryPartyID`)) left join `jb_hymanhill`.`company` on((`jb_hymanhill`.`company`.`companyID` = `jb_hymanhill`.`contact`.`primaryPartyID`)) left join `jb_hymanhill`.`prospect` on((`jb_hymanhill`.`prospect`.`prospectID` = `jb_hymanhill`.`contact`.`primaryPartyID`)) where ((`jb_hymanhill`.`mailout_pending`.`mailoutMethod` = 'email') and (`jb_hymanhill`.`mailout_pending`.`deliveryOption` = 'contactPreference') and (`jb_hymanhill`.`applicant`.`applicantID` = `jb_hymanhill`.`mailout_pending`.`applicantID`) and (`jb_hymanhill`.`contact`.`contactID` = `jb_hymanhill`.`applicant`.`contactID`) and (`jb_hymanhill`.`mailout_created`.`mailoutCreatedID` = `jb_hymanhill`.`mailout_pending`.`mailoutCreatedID`) and (((`jb_hymanhill`.`applicant`.`mailoutLastDate` + interval `jb_hymanhill`.`applicant`.`mailoutFrequency` hour) <= <cache>(now())) or (`jb_hymanhill`.`applicant`.`mailoutLastDate` = '0000-00-00 00:00:00') or (`jb_hymanhill`.`applicant`.`mailoutFrequency` = '0')) and isnull(`jb_hymanhill`.`mailout_pending`.`instantSendID`) and (((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'I') and (`jb_hymanhill`.`individual`.`individualID` is not null)) or ((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'C') and (`jb_hymanhill`.`company`.`companyID` is not null)) or ((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'P') and (`jb_hymanhill`.`prospect`.`prospectID` is not null)))) group by `jb_hymanhill`.`mailout_pending`.`mailoutCreatedID`,`jb_hymanhill`.`mailout_pending`.`applicantID` union all select 'adhoc' AS `mailoutType`,`jb_hymanhill`.`adhoc`.`mailoutID` AS `mailoutID`,'' AS `mailoutCreatedID`,`jb_hymanhill`.`adhoc_recipient_email`.`uniqueType` AS `uniqueType`,`jb_hymanhill`.`adhoc_recipient_email`.`uniqueID` AS `uniqueID`,'' AS ``,'' AS ``,`jb_hymanhill`.`adhoc`.`mailoutFrom` AS `mailoutFrom`,`jb_hymanhill`.`adhoc`.`mailoutFromOffice` AS `mailoutFromOffice`,`jb_hymanhill`.`adhoc`.`recordCreatedBy` AS `mailoutOwner`,`jb_hymanhill`.`adhoc`.`dateCreated` AS `dateCreated`,`jb_hymanhill`.`adhoc`.`recordCreatedBy` AS `recordCreatedBy`,'' AS ``,'' AS ``,`jb_hymanhill`.`adhoc_recipient_email`.`uniqueID` AS `contactID`,`jb_hymanhill`.`adhoc_recipient_email`.`emailTo` AS `name`,`jb_hymanhill`.`adhoc_recipient_email`.`emailToAddress` AS `emailAddress`,'' AS ``,`jb_hymanhill`.`adhoc`.`recordCreatedBy` AS `recordCreatedBy` from `jb_hymanhill`.`adhoc` join `jb_hymanhill`.`adhoc_recipient_email` join `jb_hymanhill`.`adhoc_email` join `jb_hymanhill`.`contact` left join `jb_hymanhill`.`individual` on((`jb_hymanhill`.`individual`.`individualID` = `jb_hymanhill`.`contact`.`primaryPartyID`)) left join `jb_hymanhill`.`company` on((`jb_hymanhill`.`company`.`companyID` = `jb_hymanhill`.`contact`.`primaryPartyID`)) left join `jb_hymanhill`.`prospect` on((`jb_hymanhill`.`prospect`.`prospectID` = `jb_hymanhill`.`contact`.`primaryPartyID`)) where ((`jb_hymanhill`.`adhoc_recipient_email`.`status` = '') and (`jb_hymanhill`.`contact`.`contactID` = `jb_hymanhill`.`adhoc_recipient_email`.`uniqueID`) and (`jb_hymanhill`.`adhoc_email`.`mailoutID` = `jb_hymanhill`.`adhoc_recipient_email`.`mailoutID`) and (`jb_hymanhill`.`adhoc`.`mailoutID` = `jb_hymanhill`.`adhoc_recipient_email`.`mailoutID`) and ((`jb_hymanhill`.`adhoc_recipient_email`.`uniqueType` = 'contact') or (`jb_hymanhill`.`adhoc_recipient_email`.`uniqueType` = 'contractor')) and ((`jb_hymanhill`.`adhoc`.`deliveryOption` = 'now') or ((`jb_hymanhill`.`adhoc`.`deliveryOption` = 'endOfToday') and (date_format(`jb_hymanhill`.`adhoc`.`dateCreated`,'%Y-%m-%d') < <cache>(curdate()))) or ((`jb_hymanhill`.`adhoc`.`deliveryOption` = 'endOfWeek') and ((`jb_hymanhill`.`adhoc`.`dateCreated` + interval (((6 - dayofweek(`jb_hymanhill`.`adhoc`.`dateCreated`)) + 7) % 7) day) <= <cache>(curdate())))) and (((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'I') and (`jb_hymanhill`.`individual`.`individualID` is not null)) or ((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'C') and (`jb_hymanhill`.`company`.`companyID` is not null)) or ((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'P') and (`jb_hymanhill`.`prospect`.`prospectID` is not null)))) union all select 'adhoc' AS `mailoutType`,`jb_hymanhill`.`adhoc`.`mailoutID` AS `mailoutID`,'' AS `mailoutCreatedID`,`jb_hymanhill`.`adhoc_recipient_email`.`uniqueType` AS `uniqueType`,`jb_hymanhill`.`adhoc_recipient_email`.`uniqueID` AS `uniqueID`,'' AS ``,'' AS ``,`jb_hymanhill`.`adhoc`.`mailoutFrom` AS `mailoutFrom`,`jb_hymanhill`.`adhoc`.`mailoutFromOffice` AS `mailoutFromOffice`,`jb_hymanhill`.`adhoc`.`recordCreatedBy` AS `mailoutOwner`,`jb_hymanhill`.`adhoc`.`dateCreated` AS `dateCreated`,`jb_hymanhill`.`adhoc`.`recordCreatedBy` AS `recordCreatedBy`,'' AS ``,'' AS ``,`jb_hymanhill`.`contact`.`contactID` AS `contactID`,`jb_hymanhill`.`adhoc_recipient_email`.`emailTo` AS `name`,`jb_hymanhill`.`adhoc_recipient_email`.`emailToAddress` AS `emailAddress`,'' AS ``,`jb_hymanhill`.`adhoc`.`recordCreatedBy` AS `recordCreatedBy` from `jb_hymanhill`.`adhoc` join `jb_hymanhill`.`adhoc_recipient_email` join `jb_hymanhill`.`adhoc_email` join `jb_hymanhill`.`applicant` join `jb_hymanhill`.`contact` left join `jb_hymanhill`.`individual` on((`jb_hymanhill`.`individual`.`individualID` = `jb_hymanhill`.`contact`.`primaryPartyID`)) left join `jb_hymanhill`.`company` on((`jb_hymanhill`.`company`.`companyID` = `jb_hymanhill`.`contact`.`primaryPartyID`)) left join `jb_hymanhill`.`prospect` on((`jb_hymanhill`.`prospect`.`prospectID` = `jb_hymanhill`.`contact`.`primaryPartyID`)) where ((`jb_hymanhill`.`adhoc_recipient_email`.`uniqueType` = 'applicant') and (`jb_hymanhill`.`adhoc_recipient_email`.`status` = '') and (`jb_hymanhill`.`applicant`.`applicantID` = `jb_hymanhill`.`adhoc_recipient_email`.`uniqueID`) and (`jb_hymanhill`.`contact`.`contactID` = `jb_hymanhill`.`applicant`.`contactID`) and (`jb_hymanhill`.`adhoc_email`.`mailoutID` = `jb_hymanhill`.`adhoc_recipient_email`.`mailoutID`) and (`jb_hymanhill`.`adhoc`.`mailoutID` = `jb_hymanhill`.`adhoc_recipient_email`.`mailoutID`) and ((`jb_hymanhill`.`adhoc`.`deliveryOption` = 'now') or ((`jb_hymanhill`.`adhoc`.`deliveryOption` = 'endOfToday') and (date_format(`jb_hymanhill`.`adhoc`.`dateCreated`,'%Y-%m-%d') < <cache>(curdate()))) or ((`jb_hymanhill`.`adhoc`.`deliveryOption` = 'endOfWeek') and ((`jb_hymanhill`.`adhoc`.`dateCreated` + interval (((6 - dayofweek(`jb_hymanhill`.`adhoc`.`dateCreated`)) + 7) % 7) day) <= <cache>(curdate())))) and (((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'I') and (`jb_hymanhill`.`individual`.`individualID` is not null)) or ((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'C') and (`jb_hymanhill`.`company`.`companyID` is not null)) or ((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'P') and (`jb_hymanhill`.`prospect`.`prospectID` is not null)))) union all select 'adhoc' AS `mailoutType`,`jb_hymanhill`.`adhoc`.`mailoutID` AS `mailoutID`,'' AS `mailoutCreatedID`,`jb_hymanhill`.`adhoc_recipient_email`.`uniqueType` AS `uniqueType`,`jb_hymanhill`.`adhoc_recipient_email`.`uniqueID` AS `uniqueID`,'' AS ``,'' AS ``,`jb_hymanhill`.`adhoc`.`mailoutFrom` AS `mailoutFrom`,`jb_hymanhill`.`adhoc`.`mailoutFromOffice` AS `mailoutFromOffice`,`jb_hymanhill`.`adhoc`.`recordCreatedBy` AS `mailoutOwner`,`jb_hymanhill`.`adhoc`.`dateCreated` AS `dateCreated`,`jb_hymanhill`.`adhoc`.`recordCreatedBy` AS `recordCreatedBy`,'' AS ``,'' AS ``,`jb_hymanhill`.`contact`.`contactID` AS `contactID`,`jb_hymanhill`.`adhoc_recipient_email`.`emailTo` AS `name`,`jb_hymanhill`.`adhoc_recipient_email`.`emailToAddress` AS `emailAddress`,'' AS ``,`jb_hymanhill`.`adhoc`.`recordCreatedBy` AS `recordCreatedBy` from `jb_hymanhill`.`adhoc` join `jb_hymanhill`.`adhoc_recipient_email` join `jb_hymanhill`.`adhoc_email` join `jb_hymanhill`.`owner` join `jb_hymanhill`.`contact` left join `jb_hymanhill`.`individual` on((`jb_hymanhill`.`individual`.`individualID` = `jb_hymanhill`.`contact`.`primaryPartyID`)) left join `jb_hymanhill`.`company` on((`jb_hymanhill`.`company`.`companyID` = `jb_hymanhill`.`contact`.`primaryPartyID`)) left join `jb_hymanhill`.`prospect` on((`jb_hymanhill`.`prospect`.`prospectID` = `jb_hymanhill`.`contact`.`primaryPartyID`)) where ((`jb_hymanhill`.`adhoc_recipient_email`.`uniqueType` = 'owner') and (`jb_hymanhill`.`adhoc_recipient_email`.`status` = '') and (`jb_hymanhill`.`contact`.`contactID` = `jb_hymanhill`.`owner`.`contactID`) and (`jb_hymanhill`.`owner`.`ownerID` = `jb_hymanhill`.`adhoc_recipient_email`.`uniqueID`) and (`jb_hymanhill`.`adhoc_email`.`mailoutID` = `jb_hymanhill`.`adhoc_recipient_email`.`mailoutID`) and (`jb_hymanhill`.`adhoc`.`mailoutID` = `jb_hymanhill`.`adhoc_recipient_email`.`mailoutID`) and ((`jb_hymanhill`.`adhoc`.`deliveryOption` = 'now') or ((`jb_hymanhill`.`adhoc`.`deliveryOption` = 'endOfToday') and (date_format(`jb_hymanhill`.`adhoc`.`dateCreated`,'%Y-%m-%d') < <cache>(curdate()))) or ((`jb_hymanhill`.`adhoc`.`deliveryOption` = 'endOfWeek') and ((`jb_hymanhill`.`adhoc`.`dateCreated` + interval (((6 - dayofweek(`jb_hymanhill`.`adhoc`.`dateCreated`)) + 7) % 7) day) <= <cache>(curdate())))) and (((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'I') and (`jb_hymanhill`.`individual`.`individualID` is not null)) or ((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'C') and (`jb_hymanhill`.`company`.`companyID` is not null)) or ((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'P') and (`jb_hymanhill`.`prospect`.`prospectID` is not null)))) union all select 'adhoc' AS `mailoutType`,`jb_hymanhill`.`adhoc`.`mailoutID` AS `mailoutID`,'' AS `mailoutCreatedID`,`jb_hymanhill`.`adhoc_recipient_email`.`uniqueType` AS `uniqueType`,`jb_hymanhill`.`adhoc_recipient_email`.`uniqueID` AS `uniqueID`,'' AS ``,'' AS ``,`jb_hymanhill`.`adhoc`.`mailoutFrom` AS `mailoutFrom`,`jb_hymanhill`.`adhoc`.`mailoutFromOffice` AS `mailoutFromOffice`,`jb_hymanhill`.`adhoc`.`recordCreatedBy` AS `mailoutOwner`,`jb_hymanhill`.`adhoc`.`dateCreated` AS `dateCreated`,`jb_hymanhill`.`adhoc`.`recordCreatedBy` AS `recordCreatedBy`,'' AS ``,'' AS ``,`jb_hymanhill`.`contact`.`contactID` AS `contactID`,`jb_hymanhill`.`adhoc_recipient_email`.`emailTo` AS `name`,`jb_hymanhill`.`adhoc_recipient_email`.`emailToAddress` AS `emailAddress`,'' AS ``,`jb_hymanhill`.`adhoc`.`recordCreatedBy` AS `recordCreatedBy` from `jb_hymanhill`.`adhoc` join `jb_hymanhill`.`adhoc_recipient_email` join `jb_hymanhill`.`adhoc_email` join `jb_hymanhill`.`owner_potential` join `jb_hymanhill`.`contact` left join `jb_hymanhill`.`individual` on((`jb_hymanhill`.`individual`.`individualID` = `jb_hymanhill`.`contact`.`primaryPartyID`)) left join `jb_hymanhill`.`company` on((`jb_hymanhill`.`company`.`companyID` = `jb_hymanhill`.`contact`.`primaryPartyID`)) left join `jb_hymanhill`.`prospect` on((`jb_hymanhill`.`prospect`.`prospectID` = `jb_hymanhill`.`contact`.`primaryPartyID`)) where ((`jb_hymanhill`.`adhoc_recipient_email`.`uniqueType` = 'potentialOwner') and (`jb_hymanhill`.`adhoc_recipient_email`.`status` = '') and (`jb_hymanhill`.`adhoc_email`.`mailoutID` = `jb_hymanhill`.`adhoc_recipient_email`.`mailoutID`) and (`jb_hymanhill`.`adhoc`.`mailoutID` = `jb_hymanhill`.`adhoc_recipient_email`.`mailoutID`) and ((`jb_hymanhill`.`adhoc`.`deliveryOption` = 'now') or ((`jb_hymanhill`.`adhoc`.`deliveryOption` = 'endOfToday') and (date_format(`jb_hymanhill`.`adhoc`.`dateCreated`,'%Y-%m-%d') < <cache>(curdate()))) or ((`jb_hymanhill`.`adhoc`.`deliveryOption` = 'endOfWeek') and ((`jb_hymanhill`.`adhoc`.`dateCreated` + interval (((6 - dayofweek(`jb_hymanhill`.`adhoc`.`dateCreated`)) + 7) % 7) day) <= <cache>(curdate())))) and (`jb_hymanhill`.`contact`.`contactID` = `jb_hymanhill`.`owner_potential`.`contactID`) and (((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'I') and (`jb_hymanhill`.`individual`.`individualID` is not null)) or ((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'C') and (`jb_hymanhill`.`company`.`companyID` is not null)) or ((`jb_hymanhill`.`contact`.`primaryPartyType` = _latin1'P') and (`jb_hymanhill`.`prospect`.`prospectID` is not null))) and (`jb_hymanhill`.`owner_potential`.`potentialOwnerID` = `jb_hymanhill`.`adhoc_recipient_email`.`uniqueID`)) union all select 'adhoc' AS `mailoutType`,`jb_hymanhill`.`adhoc`.`mailoutID` AS `mailoutID`,'' AS `mailoutCreatedID`,`jb_hymanhill`.`adhoc_recipient_email`.`uniqueType` AS `uniqueType`,`jb_hymanhill`.`adhoc_recipient_email`.`uniqueID` AS `uniqueID`,'' AS ``,'' AS ``,`jb_hymanhill`.`adhoc`.`mailoutFrom` AS `mailoutFrom`,`jb_hymanhill`.`adhoc`.`mailoutFromOffice` AS `mailoutFromOffice`,`jb_hymanhill`.`adhoc`.`recordCreatedBy` AS `mailoutOwner`,`jb_hymanhill`.`adhoc`.`dateCreated` AS `dateCreated`,`jb_hymanhill`.`adhoc`.`recordCreatedBy` AS `recordCreatedBy`,'' AS ``,'' AS ``,'0' AS `contactID`,`jb_hymanhill`.`adhoc_recipient_email`.`emailTo` AS `name`,`jb_hymanhill`.`adhoc_recipient_email`.`emailToAddress` AS `emailAddress`,'' AS ``,`jb_hymanhill`.`adhoc`.`recordCreatedBy` AS `recordCreatedBy` from `jb_hymanhill`.`adhoc` join `jb_hymanhill`.`adhoc_recipient_email` join `jb_hymanhill`.`adhoc_email` where ((`jb_hymanhill`.`adhoc_recipient_email`.`uniqueType` = 'custom') and (`jb_hymanhill`.`adhoc_recipient_email`.`status` = '') and (`jb_hymanhill`.`adhoc_email`.`mailoutID` = `jb_hymanhill`.`adhoc_recipient_email`.`mailoutID`) and (`jb_hymanhill`.`adhoc`.`mailoutID` = `jb_hymanhill`.`adhoc_recipient_email`.`mailoutID`) and ((`jb_hymanhill`.`adhoc`.`deliveryOption` = 'now') or ((`jb_hymanhill`.`adhoc`.`deliveryOption` = 'endOfToday') and (date_format(`jb_hymanhill`.`adhoc`.`dateCreated`,'%Y-%m-%d') < <cache>(curdate()))))) order by `mailoutType`,`contactID`,`dateCreated`