Page 1 of 1

SQL EXPERT WANTED !

Posted: Fri Jul 13, 2018 4:58 pm
by michael2820
Weeks ago MarketinSG was nice to tell us
SQL: "emails from customers who bought model ABC"
SELECT email FROM `order` o LEFT JOIN order_product op ON op.order_id = o.order_id WHERE op.model = 'ABC'
The above will get you the emails for customers who purchased product model 'ABC'

Ny next question is
SQL command to extract emails customers who bought model ABC FROM order_id 3.000 to order_id 4.000
3.000 and 4.000 are just example values.
Why? I want to email customers who purchased product say 2 years ago and ask about their experience etc.
Highly appreciated if you know that
Michael, Denmark

Re: SQL: "emails from customers who bought model ABC AND order_id range"

Posted: Fri Jul 13, 2018 6:26 pm
by OSWorX
And why not order date from - until ?
Would that not be better, because how do you know the order id?

SQL EXPERT WANTED !

Posted: Fri Jul 13, 2018 6:34 pm
by michael2820
I thought U were on vacation :crazy: I have all my orders in folder in thunderbird - thousands - and all my emails display order number and date in the same line, so easy to see dates. Numbers are much easier to work with than dates. It is NOT important with exact dates, just approx how many months ago. I sell water filters and the filters need to be change every 6-12 months and I want to remind my customers about that. I do NOT like recurring products. I want that my customers decide for themselves - respect.

I will keep track probably in excel file, when I sent what email to whom.

I use the best add on I have seen for OC Newsletter Enhancements
That software is worth hundreds of USD, but the owner does not know that ;-)
Gruesse aus DK

Re: SQL EXPERT WANTED !

Posted: Wed Sep 05, 2018 5:00 pm
by uebz
please reply only with verified data, that is: no 'I think', 'maybe' etc.---
probably explains the lack of replies...but I'll pretend not to see that.

1. You should seriously be using dates :p who wants to check emails and corresponding IDs each and everytime
2. With dates you can just do a 6 months from today and 12 months from today queries :p or 247 days from today anyhow...

Without having the database to play with , thus I have no verified data but...

SELECT email FROM `order` o LEFT JOIN order_product op ON op.order_id = o.order_id WHERE op.model = 'ABC' AND o.order_id > 3000 AND o.order_id < 4000

Re: SQL EXPERT WANTED !

Posted: Wed Sep 05, 2018 5:10 pm
by michael2820
thank you, I now have coder working for me and he makes this and other things