Selecting Only Records that Match.

tricky situation

[tbarbedo]

I have the following select statement...

SELECT dm.dm_id, dm.subject, dm.first_read, dm.sent,
CONCAT(u.firstname, ' ', u.lastname) AS dm_from_name
FROM portal_direct_messages AS dm,
portal_users AS u
WHERE dm.dm_to = '%d'
AND dm.dm_from = u.user_id
AND dm.deleted IS NULL

This selects all direct messages for a specific user.

What I want to to do is to check if the field dm.first_read IS NOT NULL meaning the message has been read already...

If it is NOT NULL I would like to only select messages from the past 15 days...

Can someone help me get this done all in one query?

Thanks...

[coopster]

In addition to the existing WHERE clause you have there? Just add it in, assuming it can indeed be either NULL or what you explained here ...

AND ( 
 dm.first_read IS NULL 
 OR 
 ( 
   dm.first_read IS NOT NULL 
   AND 
   yourMessageDateColumn >= CURRENT_DATE - INTERVAL 15 DAY 
 ) 
)

I didn't test that or check the syntax, but it should be close.