SQL using NOT EXISTS

Question

I am trying to write an SQL query that returns all student email addresses for clients who have had a new invoice since April 1 and have not yet scheduled a delivery for this fall. This is returning an empty set even though I know there are entries that meet these conditions. I've tried a few different things with no luck, is there a way to do this?

SELECT clients.studentEmail 
FROM `clients`, `invoices` 
WHERE clients.clientId = invoices.clientId 
AND invoices.datePosted > "2013-04-01" 
AND NOT EXISTS 
    (SELECT * 
    FROM appointments, clients
    WHERE clients.clientId = appointments.clientId 
    AND appointments.serviceDirection = "Delivery" 
    AND appointments.date > '2013-07-01')

Answer 1

You have to relate your not exists subquery to the outer query. For example:

select  clients.studentemail 
from    clients c
join    invoices i
on      c.clientid = i.clientid 
where   invoices.dateposted > "2013-04-01" 
        and not exists 
        (
        select  * 
        from    appointments a
        where   c.clientid = a.clientid -- Relates outer to inner query
                and a.servicedirection = "delivery" 
                and a.date > '2013-07-01')
        )