Find newsletter subscribers that are not in the UK

[14Steve14]

My mailing list is getting to the limit of the free subscribers allowed so I need to trim the list somehow. I have already got all the non openers and non readers from the system available at Mailchimp.

I need some sql code that will produce a list of customers that are newsletter subscribers, but do not live in the UK. All I really need are their names and email addresses. I need to export the data into a spreadsheet so over time they can be unsubscribed after checking that they are also unsubscribed in the mailing list data. NOTE I do not want to directly delete them from the database.

Is this even possible.

[ecartz]

Code: Select all

SELECT CONCAT_WS(' ', c.customers_firstname, c.customers_lastname) AS name, c.customers_email_address
 FROM customers c INNER JOIN address_book ab ON c.customers_id = ab.customers_id AND c.customers_default_address_id = ab.address_book_id
 WHERE c.customers_newsletter = '1' AND ab.entry_country_id != 222

[14Steve14]

Code: Select all

SELECT CONCAT_WS(' ', c.customers_firstname, c.customers_lastname) AS name, c.customers_email_address
 FROM customers c INNER JOIN address_book ab ON c.customers_id = ab.customers_id AND c.customers_default_address_id = ab.address_book_id
 WHERE c.customers_newsletter = '1' AND ab.entry_country_id != 222

ecartz Many thanks. Worked like a charm. Have saved it somewhere half save for future use.