CODEWITHSUNDEEP
mysqlsqlleft-join
Revenant
Revenant

Reputation: 2984

LEFT JOIN isn't working as expected while 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

Upvotes: 2

Views: 171

Answers (3)

Max
Max

Reputation: 842

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

Upvotes: 4

Xint0
Xint0

Reputation: 5379

The problem is that you are checking the tag date on the same query that checks the existence of a tag.

Try this:

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

Upvotes: 2

Dewfy
Dewfy

Reputation: 23614

Just realize that specifying condition under WHERE - you implement INNER JOIN logic, It is not what you have expected. So place entire WHERE section under ON of OUTER JOIN:

 ... ON s.id = t.subscription_id 
 AND t.subscription_id IS NULL AND 
 t.deliveryDate = '2011-10-01' AND 
 s.status = '2' AND 
 s.active = '1'

Upvotes: 1

Related Questions