inner / outer joins

[plork123]

hi all i have this query

SELECT distinct u.user_id, u.user_name, u.user_email
FROM tbl_users u
inner join tbl_user_options uo on (uo.user_id = u.user_id and uo.email_id = 10)
and (CAST(date_last_sent AS DATE) IS NULL and  uo.user_noreceive is null) OR (uo.user_noreceive = 0 and CAST(date_last_sent AS DATE) <= (DATE_SUB(NOW(), INTERVAL 10 DAY)))
left outer join tbl_photos p on u.user_id = p.user_id
WHERE p.user_id is null

what i'm trying to do is return a list of users who haven't got a photograph, but i need to exclude them if they have an entry in the tbl_user_options where the last time they got an email in less than 10 days ago or if they have have decided they don't want the email sent to them (user_noreceive 1 = don't want email)

this works but is ignoring the date, so if a user hasn't created a photograph, they are in the results, but i don't want them to be in the results if the date_last_sent is less than 10 days or user_noreceive = 1

thanks for any help

 

[r937]

i think you have a problem with ANDs and ORs and parentheses

try this --

select distinct 
       u.user_id
     , u.user_name
     , u.user_email
  from tbl_users u
inner 
  join tbl_user_options uo 
    on uo.user_id = u.user_id 
   and uo.email_id = 10
   and (
       (
       date_last_sent IS NULL 
   and uo.user_noreceive is null
       ) 
    or (
       CAST(date_last_sent AS DATE) 
        <= (DATE_SUB(NOW(), INTERVAL 10 DAY)))
   and uo.user_noreceive = 0 
       )
       )
left outer 
  join tbl_photos p 
    on p.user_id = u.user_id
 where p.user_id is null

r937.com | rudy.ca