SQL query to pull out Expired email addresses

[stumpy_dan]

Hi there,

 

I am looking to email my expired listing owners that they are now expired and to offer them a cheap monthly subscription option to come back. How can I get a list of their email addresses? I tried a few SQL queries but it is not quite my bag and did not give me the desired results..

 

This is what I tried:

 

SELECT DISTINCTROW
   User.email 
FROM 
   jos_users AS User
INNER JOIN
   jos_jreviews_paid_orders AS PaidOrder ON PaidOrder.user_id = User.id AND PaidOrder.order_status = "Complete" AND order_active = 0

 

Problem is this seemed to include folk that are now active also. On closer inspection i think this is because if someone has been active (e.g. on a free three month plan) and then expired and then renewed (paid a £29.99 a month annual payment) they come up twice i.e. they have both an expired and an active flag in the database i.e. two entries. If I emailed this list then I would also be emailing folk that have recently renewed with a payment and they would get quite worried.

 

I need it to be folk who are currently expired as of now if that makes sense and to exclude anyone who has renewed and since active.

 

Loving the service by the way. I have my first 16 paying customers. Early days but exciting.

 

[Alejandro]

This section is only visible with a valid subscription. If you have a valid subscription, please login.

[stumpy_dan]

This section is only visible with a valid subscription. If you have a valid subscription, please login.

[Alejandro]

This section is only visible with a valid subscription. If you have a valid subscription, please login.