I am trying to create a SELECT statement from two different tables, where I use to send messages, I don't want to send a message to someone for 1 wk after they join, however if I have already sent them a message I want to wait 10 days before sending next one.
Here's what I have:
SELECT c.*, g.resToMeeting, g.replied, g.conlevel, g.coffee, g.lastMessageSent
FROM connections c
INNER JOIN groupmembers g
ON c.Id=g.Id
AND g.groupN=244 # this decides the client I'm pulling for...(or group they own)
AND g.client=c.client
AND g.conlevel=1
AND (datediff(curdate(), g.lastMessageSent) > 10 OR datediff(curdate(), c.dateConnected) > 7)
AND c.validated=1
AND c.process_rank=0
ORDER BY c.dateAdded ASC
LIMIT 0, 200
The trouble I'm having is that it shows EITHER people who haven't joined within the last week, OR people who haven't received a messages within the last 10 days. It seems that it isn't working:
I received a record that had lastMessageSent as 2015-04-29, (which isn't 10 days ago) but the dateConnected was 2015-04-15 which was over 7 days. How can I enforce both rules "together" not either or, sometimes there is no data in lastMessageSent or dateConnected, and that should be OK.
Aucun commentaire:
Enregistrer un commentaire