Mysql LEFT JOIN does not work as expected when retrieving data from 2 tables

I have 2 tables, named subscriptions and tags. Once in a month I will have to create tags for each order.

I have to list subscriptions without a tag for the given month (2011-10-01 or 2011-09-01 etc). This query returns 0 records:

   SELECT s.id, s.active, s.status
     FROM asw_subscriptions as s
LEFT JOIN asw_tags as t ON s.id = t.subscription_id
    WHERE t.subscription_id IS NULL
      AND t.deliveryDate = '2011-10-01'
      AND s.status = '2'
      AND s.active = '1'
 ORDER BY s.id DESC
    LIMIT 0, 25

Table Structure

---

subscriptions = id (int / auto), active (enum : 0,1), status (enum : 0,1)

tags = id (int / auto), deliveryDate (date), tagNumber

---

The problem is in clausule

t.deliveryDate = '2011-10-01' AND

You have not record on the left because condition 'IS NULL' eliminates LEFT JOIN'ed records. So, above condition will eliminate all join products, because will never be true (there will be always null in t.deliveryDate.

Try something like this:

SELECT s.id, s.active, s.status
     FROM asw_subscriptions as s
WHERE s.status = '2'
      AND s.active = '1'
      AND NOT EXISTS (
         SELECT 1
         FROM asw_tags as t
         WHERE s.id = t.subscription_id
         AND t.deliveryDate = '2011-10-01'
      )
 ORDER BY s.id DESC
    LIMIT 0, 25