Sorting subscribers br country - need help with my SQL please. :)

General General Questions
1

Hi all, i have a general query run to get my subscribers from the database as follows;



select
cscart_subscribers.subscriber_id,
cscart_subscribers.email
from
cscart_subscribers,
cscart_user_mailing_lists
where
cscart_subscribers.subscriber_id = cscart_user_mailing_lists.subscriber_id and
cscart_user_mailing_lists.list_id = 1 and
cscart_user_mailing_lists.confirmed = 1;




This time, I am wanting to limit my newsletter to recipients in two countries. My query below is working, but it seems to be grabbing each email address multiple times for some reason. Can anyone help with the logic of my query? Many thanks.


select
cscart_subscribers.subscriber_id,
cscart_subscribers.email,
cscart_countries.region
from
cscart_subscribers,
cscart_user_mailing_lists,
cscart_countries
where
cscart_subscribers.subscriber_id = cscart_user_mailing_lists.subscriber_id and
cscart_user_mailing_lists.list_id = 1 and
cscart_user_mailing_lists.confirmed = 1 and
cscart_countries.region = 'AU' or
cscart_countries.region = 'NZ';

2

Maybe you can try:


select
DISTINCT cscart_subscribers.subscriber_id,
cscart_subscribers.email,
cscart_countries.region
from
cscart_subscribers,
cscart_user_mailing_lists,
cscart_countries
where
cscart_subscribers.subscriber_id = cscart_user_mailing_lists.subscriber_id and
cscart_user_mailing_lists.list_id = 1 and
cscart_user_mailing_lists.confirmed = 1 and
cscart_countries.region = 'AU' or
cscart_countries.region = 'NZ';

3

Thanks guys :) I’ll give it a try.

4

Unfortunately it throws a syntax error.



#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT cscart_subscribers.email, cscart_countries.region from cscart_subsc' at line 3

5

Sorry, the above code is updated.

6

Thank you, that worked! :-o

7

You could always export the tables to Excel and then filter based on containing cells.

Reason I do that is 'bigpond.com' email addresses are not post-fixed with .au which leaves a lot of addresses out.