Getting empty result from a query

I have Client table

• ClientGuid Email Phone

Also have Activity table

• ActivityTitle Date ClientGuid

If client does some “Activity” it is saved in Activity table.

I want to get Emails of clients who did not have any activity in last three months and had activities before.

Tried this :

select Email
from Client
where NOT EXISTS (
SELECT Email
FROM Client c
LEFT JOIN Activity a on a.ClientGuid = a.ClientGuid
WHERE a.Date > DATEADD(MONTH, -3, GETDATE())

Getting empty result. What did I miss?

Answer

I don’t see why you would want a join — much less an outer join — in the subquery. This returns the emails of clients that have no activity in the past three months:

select c.Email
from Client c
where not exists (select 1
                  from Activity a 
                  where a.ClientGuid = a.ClientGuid and
                        a.Date > DATEADD(MONTH, -3, GETDATE())
                 );

However, this also returns clients who have never had any activity at all. So, you seem to want:

select c.Email
from Client c
where (select max(a.date)
       from Activity a 
       where a.ClientGuid = a.ClientGuid
      ) <= DATEADD(MONTH, -3, GETDATE());

If there are no activities, then the subquery returns NULL and the client is filtered out.