StackOverflow Q15656123

INITIAL SEARCH QUERY - searching by name for 'mikkyx':
SELECT `<name+addr table>`.`record_id`, `<name+addr table>`.`title`, `<name+addr table>`.`first_name`, `<name+addr table>`.`family_name`, `<name+addr table>`.`job_title`, `<name+addr table>`.`company_name`, `<name+addr table>`.`address_1`, `<name+addr table>`.`address_2`, `<name+addr table>`.`town_city`, `<name+addr table>`.`post_code`, `<name+addr table>`.`email`, `<name+addr table>`.`ignored`, `countries`.`country_name` FROM (`<name+addr table>`) LEFT JOIN `countries` ON `countries`.`country_id` = `<name+addr table>`.`country_id` JOIN `<name+addr table>` w2 ON `w2`.`dupe_master_id` = `<name+addr table>`.`record_id` WHERE <name+addr table>.record_id = <name+addr table>.dupe_master_id AND (w2.first_name = 'mikkyx' OR w2.family_name = 'mikkyx' OR CONCAT(w2.first_name,' ',w2.family_name) = 'mikkyx' OR CONCAT(w2.family_name,' ',w2.first_name) = 'mikkyx') GROUP BY `<name+addr table>`.`record_id`

SEARCHING FOR SUBSCRIPTIONS (executed against each record that would be returned by the above result):
SELECT `s`.*, `ut`.`title_code`, `ut`.`title_name`, `ut`.`parent_title_id`, `ss`.`subscription_status`, `sr`.`subscription_rate_name`, `cr`.`cancellation_reason`, `cs`.`cancellation_source`, `rn`.`sub_number`, `rsm`.`number_copies` FROM (`red_subscriptions` s) JOIN `red_subscription_status` ss ON `s`.`subscription_status_id` = `ss`.`subscription_status_id` LEFT JOIN `red_subscription_rates` sr ON `s`.`subscription_rate_id` = `sr`.`subscription_rate_id` JOIN `titles` ut ON `s`.`title_id` = `ut`.`title_id` LEFT JOIN `cancellation_reasons` cr ON `s`.`cancellation_reason_id` = `cr`.`cancellation_reason_id` LEFT JOIN `cancellation_sources` cs ON `s`.`cancellation_source_id` = `cs`.`cancellation_source_id` LEFT JOIN `subscription_numbers` rn ON `s`.`subscription_id` = `rn`.`subscription_id` JOIN `name_addr` w ON `s`.`record_id` = `w`.`record_id` LEFT JOIN `subscription_multicopy` rsm ON `s`.`subscription_id` = `rsm`.`subscription_id` WHERE (w.dupe_master_id IN (2428) OR w.record_id IN (2428)) AND `s`.`subscription_status_id` = 2 GROUP BY `s`.`title_id` ORDER BY `ut`.`code` ASC