mardi 5 mai 2015

Adding a constraint to both tables independently in join

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