Jump to content

  • You cannot start a new topic
  • You cannot reply to this topic

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

 
  • Scott_C
  • Senior Member
  • Members
  • Join Date: 23-Jun 11
  • 452 posts

Posted 16 December 2013 - 02:10 AM #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';


 
  • cscartrocks
  • Member
  • Members
  • Join Date: 24-Jan 11
  • 1833 posts

Posted 16 December 2013 - 02:21 AM #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';

One Step Checkout Addon - The ultimate checkout experience
Best CS-Cart SEO addon - CS-Cart SEO Ultimate Addon
PM for 365-day support and custom development service


 
  • Scott_C
  • Senior Member
  • Members
  • Join Date: 23-Jun 11
  • 452 posts

Posted 16 December 2013 - 02:24 AM #3

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

 
  • Scott_C
  • Senior Member
  • Members
  • Join Date: 23-Jun 11
  • 452 posts

Posted 16 December 2013 - 02:26 AM #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


 
  • cscartrocks
  • Member
  • Members
  • Join Date: 24-Jan 11
  • 1833 posts

Posted 16 December 2013 - 03:00 AM #5

Sorry, the above code is updated.

One Step Checkout Addon - The ultimate checkout experience
Best CS-Cart SEO addon - CS-Cart SEO Ultimate Addon
PM for 365-day support and custom development service


 
  • Scott_C
  • Senior Member
  • Members
  • Join Date: 23-Jun 11
  • 452 posts

Posted 16 December 2013 - 03:19 AM #6

Thank you, that worked! :-o

 

Posted 16 December 2013 - 10:01 PM #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.
I've moved on from CS-Cart to WooC******** - If you need anything I can be of little help.