users

SELECT
  IF(user.sex='m','Mr','Mrs') AS 'Title',
  user.username AS 'First Name',
  user.lastname AS 'Surname',
  '' AS 'AddressLine1',
  '' AS 'AddressLine2',
  '' AS 'AddressLine3',
  '' AS 'AddressLine4',
  '' AS 'AddressLine5',
  '' AS 'Town',
  LOWER(user.country) AS 'County',
  '' AS 'Postcode',
  CASE UPPER(user.country)
    WHEN 'RU' THEN 'Russian Federation (the)'
    WHEN 'JP' THEN 'Japan'
    WHEN 'KR' THEN 'Korea (the Republic of)'
    WHEN 'CN' THEN 'China'
    WHEN 'EN' THEN 'United Kingdom (the)'
    WHEN 'AF' THEN 'Afghanistan'
    WHEN 'AX' THEN 'Åland Islands'
    WHEN 'AL' THEN 'Albania'
    WHEN 'DZ' THEN 'Algeria'
    WHEN 'AS' THEN 'American Samoa'
    WHEN 'AD' THEN 'Andorra'
    WHEN 'AO' THEN 'Angola'
    WHEN 'AI' THEN 'Anguilla'
    WHEN 'AQ' THEN 'Antarctica'
    WHEN 'AG' THEN 'Antigua and Barbuda'
    WHEN 'AR' THEN 'Argentina'
    WHEN 'AM' THEN 'Armenia'
    WHEN 'AW' THEN 'Aruba'
    WHEN 'AU' THEN 'Australia'
    WHEN 'AT' THEN 'Austria'
    WHEN 'AZ' THEN 'Azerbaijan'
    WHEN 'BS' THEN 'Bahamas (the)'
    WHEN 'BH' THEN 'Bahrain'
    WHEN 'BD' THEN 'Bangladesh'
    WHEN 'BB' THEN 'Barbados'
    WHEN 'BY' THEN 'Belarus'
    WHEN 'BE' THEN 'Belgium'
    WHEN 'BZ' THEN 'Belize'
    WHEN 'BJ' THEN 'Benin'
    WHEN 'BM' THEN 'Bermuda'
    WHEN 'BT' THEN 'Bhutan'
    WHEN 'BO' THEN 'Bolivia, Plurinational State of'
    WHEN 'BQ' THEN 'Bonaire, Sint Eustatius and Saba'
    WHEN 'BA' THEN 'Bosnia and Herzegovina'
    WHEN 'BW' THEN 'Botswana'
    WHEN 'BV' THEN 'Bouvet Island'
    WHEN 'BR' THEN 'Brazil'
    WHEN 'BQ' THEN 'British Antarctic Territory'
    WHEN 'IO' THEN 'British Indian Ocean Territory (the)'
    WHEN 'BN' THEN 'Brunei Darussalam'
    WHEN 'BG' THEN 'Bulgaria'
    WHEN 'BF' THEN 'Burkina Faso'
    WHEN 'BU' THEN 'Burma'
    WHEN 'BI' THEN 'Burundi'
    WHEN 'BY' THEN 'Byelorussian SSR'
    WHEN 'CV' THEN 'Cabo Verde'
    WHEN 'KH' THEN 'Cambodia'
    WHEN 'CM' THEN 'Cameroon'
    WHEN 'CA' THEN 'Canada'
    WHEN 'CT' THEN 'Canton and Enderbury Islands'
    WHEN 'KY' THEN 'Cayman Islands (the)'
    WHEN 'CF' THEN 'Central African Republic (the)'
    WHEN 'TD' THEN 'Chad'
    WHEN 'CL' THEN 'Chile'
    WHEN 'CX' THEN 'Christmas Island'
    WHEN 'CC' THEN 'Cocos (Keeling) Islands (the)'
    WHEN 'CO' THEN 'Colombia'
    WHEN 'KM' THEN 'Comoros'
    WHEN 'CG' THEN 'Congo'
    WHEN 'CD' THEN 'Congo (the Democratic Republic of the)'
    WHEN 'CK' THEN 'Cook Islands (the)'
    WHEN 'CR' THEN 'Costa Rica'
    WHEN 'CI' THEN 'Côte d\'Ivoire'
    WHEN 'HR' THEN 'Croatia'
    WHEN 'CU' THEN 'Cuba'
    WHEN 'CW' THEN 'Curaçao'
    WHEN 'CY' THEN 'Cyprus'
    WHEN 'CZ' THEN 'Czech Republic (the)'
    WHEN 'CS' THEN 'Czechoslovakia'
    WHEN 'DY' THEN 'Dahomey'
    WHEN 'DK' THEN 'Denmark'
    WHEN 'DJ' THEN 'Djibouti'
    WHEN 'DM' THEN 'Dominica'
    WHEN 'DO' THEN 'Dominican Republic (the)'
    WHEN 'NQ' THEN 'Dronning Maud Land'
    WHEN 'TP' THEN 'East Timor'
    WHEN 'EC' THEN 'Ecuador'
    WHEN 'EG' THEN 'Egypt'
    WHEN 'SV' THEN 'El Salvador'
    WHEN 'GQ' THEN 'Equatorial Guinea'
    WHEN 'ER' THEN 'Eritrea'
    WHEN 'EE' THEN 'Estonia'
    WHEN 'ET' THEN 'Ethiopia'
    WHEN 'FK' THEN 'Falkland Islands (the) [Malvinas]'
    WHEN 'FO' THEN 'Faroe Islands (the)'
    WHEN 'FJ' THEN 'Fiji'
    WHEN 'FI' THEN 'Finland'
    WHEN 'FR' THEN 'France'
    WHEN 'FX' THEN 'France, Metropolitan'
    WHEN 'AI' THEN 'French Afars and Issas'
    WHEN 'GF' THEN 'French Guiana'
    WHEN 'PF' THEN 'French Polynesia'
    WHEN 'FQ' THEN 'French Southern and Antarctic Territories'
    WHEN 'TF' THEN 'French Southern Territories (the)'
    WHEN 'GA' THEN 'Gabon'
    WHEN 'GM' THEN 'Gambia (The)'
    WHEN 'GE' THEN 'Georgia'
    WHEN 'DD' THEN 'German Democratic Republic'
    WHEN 'DE' THEN 'Germany'
    WHEN 'GH' THEN 'Ghana'
    WHEN 'GI' THEN 'Gibraltar'
    WHEN 'GE' THEN 'Gilbert and Ellice Islands'
    WHEN 'GR' THEN 'Greece'
    WHEN 'GL' THEN 'Greenland'
    WHEN 'GD' THEN 'Grenada'
    WHEN 'GP' THEN 'Guadeloupe'
    WHEN 'GU' THEN 'Guam'
    WHEN 'GT' THEN 'Guatemala'
    WHEN 'GG' THEN 'Guernsey'
    WHEN 'GN' THEN 'Guinea'
    WHEN 'GW' THEN 'Guinea-Bissau'
    WHEN 'GY' THEN 'Guyana'
    WHEN 'HT' THEN 'Haiti'
    WHEN 'HM' THEN 'Heard Island and McDonald Islands'
    WHEN 'VA' THEN 'Holy See (the) [Vatican City State]'
    WHEN 'HN' THEN 'Honduras'
    WHEN 'HK' THEN 'Hong Kong'
    WHEN 'HU' THEN 'Hungary'
    WHEN 'IS' THEN 'Iceland'
    WHEN 'IN' THEN 'India'
    WHEN 'ID' THEN 'Indonesia'
    WHEN 'IR' THEN 'Iran (the Islamic Republic of)'
    WHEN 'IQ' THEN 'Iraq'
    WHEN 'IE' THEN 'Ireland'
    WHEN 'IM' THEN 'Isle of Man'
    WHEN 'IL' THEN 'Israel'
    WHEN 'IT' THEN 'Italy'
    WHEN 'JM' THEN 'Jamaica'
    WHEN 'JE' THEN 'Jersey'
    WHEN 'JT' THEN 'Johnston Island'
    WHEN 'JO' THEN 'Jordan'
    WHEN 'KZ' THEN 'Kazakhstan'
    WHEN 'KE' THEN 'Kenya'
    WHEN 'KI' THEN 'Kiribati'
    WHEN 'KP' THEN 'Korea (the Democratic People\'s Republic of)'
    WHEN 'KW' THEN 'Kuwait'
    WHEN 'KG' THEN 'Kyrgyzstan'
    WHEN 'LA' THEN 'Lao People\'s Democratic Republic (the)'
    WHEN 'LV' THEN 'Latvia'
    WHEN 'LB' THEN 'Lebanon'
    WHEN 'LS' THEN 'Lesotho'
    WHEN 'LR' THEN 'Liberia'
    WHEN 'LY' THEN 'Libya'
    WHEN 'LI' THEN 'Liechtenstein'
    WHEN 'LT' THEN 'Lithuania'
    WHEN 'LU' THEN 'Luxembourg'
    WHEN 'MO' THEN 'Macao'
    WHEN 'MK' THEN 'Macedonia (the former Yugoslav Republic of)'
    WHEN 'MG' THEN 'Madagascar'
    WHEN 'MW' THEN 'Malawi'
    WHEN 'MY' THEN 'Malaysia'
    WHEN 'MV' THEN 'Maldives'
    WHEN 'ML' THEN 'Mali'
    WHEN 'MT' THEN 'Malta'
    WHEN 'MH' THEN 'Marshall Islands (the)'
    WHEN 'MQ' THEN 'Martinique'
    WHEN 'MR' THEN 'Mauritania'
    WHEN 'MU' THEN 'Mauritius'
    WHEN 'YT' THEN 'Mayotte'
    WHEN 'MX' THEN 'Mexico'
    WHEN 'FM' THEN 'Micronesia (the Federated States of)'
    WHEN 'MI' THEN 'Midway Islands'
    WHEN 'MD' THEN 'Moldova (the Republic of)'
    WHEN 'MC' THEN 'Monaco'
    WHEN 'MN' THEN 'Mongolia'
    WHEN 'ME' THEN 'Montenegro'
    WHEN 'MS' THEN 'Montserrat'
    WHEN 'MA' THEN 'Morocco'
    WHEN 'MZ' THEN 'Mozambique'
    WHEN 'MM' THEN 'Myanmar'
    WHEN 'NA' THEN 'Namibia'
    WHEN 'NR' THEN 'Nauru'
    WHEN 'NP' THEN 'Nepal'
    WHEN 'NL' THEN 'Netherlands (the)'
    WHEN 'AN' THEN 'Netherlands Antilles'
    WHEN 'NT' THEN 'Neutral Zone'
    WHEN 'NC' THEN 'New Caledonia'
    WHEN 'NH' THEN 'New Hebrides'
    WHEN 'NZ' THEN 'New Zealand'
    WHEN 'NI' THEN 'Nicaragua'
    WHEN 'NE' THEN 'Niger (the)'
    WHEN 'NG' THEN 'Nigeria'
    WHEN 'NU' THEN 'Niue'
    WHEN 'NF' THEN 'Norfolk Island'
    WHEN 'MP' THEN 'Northern Mariana Islands (the)'
    WHEN 'NO' THEN 'Norway'
    WHEN 'OM' THEN 'Oman'
    WHEN 'PC' THEN 'Pacific Islands (Trust Territory)'
    WHEN 'PK' THEN 'Pakistan'
    WHEN 'PW' THEN 'Palau'
    WHEN 'PS' THEN 'Palestine, State of'
    WHEN 'PA' THEN 'Panama'
    WHEN 'PZ' THEN 'Panama Canal Zone'
    WHEN 'PG' THEN 'Papua New Guinea'
    WHEN 'PY' THEN 'Paraguay'
    WHEN 'PE' THEN 'Peru'
    WHEN 'PH' THEN 'Philippines (the)'
    WHEN 'PN' THEN 'Pitcairn'
    WHEN 'PL' THEN 'Poland'
    WHEN 'PT' THEN 'Portugal'
    WHEN 'PR' THEN 'Puerto Rico'
    WHEN 'QA' THEN 'Qatar'
    WHEN 'RE' THEN 'Réunion'
    WHEN 'RO' THEN 'Romania'
    WHEN 'RW' THEN 'Rwanda'
    WHEN 'BL' THEN 'Saint Barthélemy'
    WHEN 'SH' THEN 'Saint Helena, Ascension and Tristan da Cunha'
    WHEN 'KN' THEN 'Saint Kitts and Nevis'
    WHEN 'LC' THEN 'Saint Lucia'
    WHEN 'MF' THEN 'Saint Martin (French part)'
    WHEN 'PM' THEN 'Saint Pierre and Miquelon'
    WHEN 'VC' THEN 'Saint Vincent and the Grenadines'
    WHEN 'WS' THEN 'Samoa'
    WHEN 'SM' THEN 'San Marino'
    WHEN 'ST' THEN 'Sao Tome and Principe'
    WHEN 'SA' THEN 'Saudi Arabia'
    WHEN 'SN' THEN 'Senegal'
    WHEN 'RS' THEN 'Serbia'
    WHEN 'CS' THEN 'Serbia and Montenegro'
    WHEN 'SC' THEN 'Seychelles'
    WHEN 'SL' THEN 'Sierra Leone'
    WHEN 'SK' THEN 'Sikkim'
    WHEN 'SG' THEN 'Singapore'
    WHEN 'SX' THEN 'Sint Maarten (Dutch part)'
    WHEN 'SK' THEN 'Slovakia'
    WHEN 'SI' THEN 'Slovenia'
    WHEN 'SB' THEN 'Solomon Islands (the)'
    WHEN 'SO' THEN 'Somalia'
    WHEN 'ZA' THEN 'South Africa'
    WHEN 'GS' THEN 'South Georgia and the South Sandwich Islands'
    WHEN 'SS' THEN 'South Sudan '
    WHEN 'RH' THEN 'Southern Rhodesia'
    WHEN 'ES' THEN 'Spain'
    WHEN 'LK' THEN 'Sri Lanka'
    WHEN 'SD' THEN 'Sudan (the)'
    WHEN 'SR' THEN 'Suriname'
    WHEN 'SJ' THEN 'Svalbard and Jan Mayen'
    WHEN 'SZ' THEN 'Swaziland'
    WHEN 'SE' THEN 'Sweden'
    WHEN 'CH' THEN 'Switzerland'
    WHEN 'SY' THEN 'Syrian Arab Republic (the)'
    WHEN 'TW' THEN 'Taiwan (Province of China)'
    WHEN 'TJ' THEN 'Tajikistan'
    WHEN 'TZ' THEN 'Tanzania, United Republic of'
    WHEN 'TH' THEN 'Thailand'
    WHEN 'TL' THEN 'Timor-Leste'
    WHEN 'TG' THEN 'Togo'
    WHEN 'TK' THEN 'Tokelau'
    WHEN 'TO' THEN 'Tonga'
    WHEN 'TT' THEN 'Trinidad and Tobago'
    WHEN 'TN' THEN 'Tunisia'
    WHEN 'TR' THEN 'Turkey'
    WHEN 'TM' THEN 'Turkmenistan'
    WHEN 'TC' THEN 'Turks and Caicos Islands (the)'
    WHEN 'TV' THEN 'Tuvalu'
    WHEN 'UG' THEN 'Uganda'
    WHEN 'UA' THEN 'Ukraine'
    WHEN 'AE' THEN 'United Arab Emirates (the)'
    WHEN 'US' THEN 'United States (the)'
    WHEN 'UM' THEN 'United States Minor Outlying Islands (the)'
    WHEN 'PU' THEN 'United States Miscellaneous Pacific Islands'
    WHEN 'HV' THEN 'Upper Volta'
    WHEN 'UY' THEN 'Uruguay'
    WHEN 'SU' THEN 'USSR'
    WHEN 'UZ' THEN 'Uzbekistan'
    WHEN 'VU' THEN 'Vanuatu'
    WHEN 'VE' THEN 'Venezuela, Bolivarian Republic of '
    WHEN 'VN' THEN 'Viet Nam'
    WHEN 'VD' THEN 'Viet-Nam, Democratic Republic of'
    WHEN 'VG' THEN 'Virgin Islands (British)'
    WHEN 'VI' THEN 'Virgin Islands (U.S.)'
    WHEN 'WK' THEN 'Wake Island'
    WHEN 'WF' THEN 'Wallis and Futuna'
    WHEN 'EH' THEN 'Western Sahara*'
    WHEN 'YE' THEN 'Yemen'
    WHEN 'YD' THEN 'Yemen, Democratic'
    WHEN 'YU' THEN 'Yugoslavia'
    WHEN 'ZR' THEN 'Zaire'
    WHEN 'ZM' THEN 'Zambia'
    WHEN 'ZW' THEN 'Zimbabwe'
  END AS 'Country',
  '' AS 'Work_Telephone',
  '' AS 'Home_Telephone',
  '' AS 'Mobile_Telephone',
  user.email AS 'EmailAddress',
  DATE_FORMAT(user.dob,'%d.%m.%Y') AS 'DOB',
  UPPER(user.sex) AS 'Gender',
  IF(user.muGroupNews=1,'Y','N') AS 'MUOptOut',
  IF(user.muPartnersNews=1,'Y','N') AS 'MU3rdOptOut',
  DATE_FORMAT(FROM_UNIXTIME(user.createdAt),'%d.%m.%Y') AS 'PrivacyConsentDate',
  '' AS 'ManUtd_Fan',
  '' AS 'MediaCode',
  DATE_FORMAT(FROM_UNIXTIME(user.createdAt),'%d.%m.%Y') AS 'DateOfCapture'
FROM
  af_user AS user
  JOIN af_work AS work ON (work.userId = user.id)
WHERE
  work.status IN(3,6) AND
  user.sex IN('f','m') AND
  user.dob != '0000-00-00' AND
  user.email NOT LIKE 'donflash%' AND
  user.email NOT LIKE '%mailinator.com' AND
  user.id NOT IN(42,63,93,97,98,99,170,407,443,444,445,446,447,448,449,450,451,452,454,455,456,457,458,459,460,461,462,463,483,484,485,486,487,488,489,491,497,503,505,517,518,519,520,521,522,523,524,532,533,540,541,542,546,547,553,560,696,697,705,765,785,734,735,736,737,738,739,740,741,743,744,726,727,728,729,730,246,154,527,717,430)
GROUP BY 
  user.email