MYSQL NOT IN subselect Query

Question

I'am trying to get the inverse of a selection from an sql query. Here is my scenario, I select the payments related to a family with a query, and those who have made a donation in the last 6 months are considered active. I am interested in the ones who do not have an entry in the last 6 months.

I can select those who have made a donation in the last 6 months with:

SELECT ppl.plg_FamID
FROM pledge_plg AS ppl
WHERE  ((DATEDIFF( NOW(), ppl.plg_date ) < 180) )

I get an error ("Internal Server Error"), when I try to select the ones who have not made a donation. I use the above code inside a subselect statement inside the NOT IN function as follows:

    SELECT pp.plg_FamID
FROM pledge_plg AS pp
WHERE pp.plg_FamID NOT IN (
        SELECT ppl.plg_FamID
        FROM pledge_plg AS ppl
        WHERE  ((DATEDIFF( NOW(), ppl.plg_date ) < 180) ) 
    )
ORDER BY pp.plg_FamID ASC, pp.plg_date DESC 
LIMIT 0, 30

The full code is as follows (just for reference).

SELECT DISTINCT
    ff.fam_Envelope AS ENV, 
    ff.fam_ID AS "Family ID", 
    ff.fam_Name AS Family,
    fc.c3 AS "Active?",
    pp.plg_date,
    (DATEDIFF( NOW(), pp.plg_date ))
FROM 
    family_fam AS ff 
LEFT JOIN family_custom AS fc ON ff.fam_ID = fc.fam_ID
LEFT JOIN pledge_plg AS pp ON ff.fam_ID = pp.plg_FamID
WHERE 
    fc.c3 = "true" 
    AND pp.plg_FamID NOT IN (
        SELECT ppl.plg_FamID
        FROM pledge_plg AS ppl
        WHERE  ((DATEDIFF( NOW(), ppl.plg_date ) < 180) )
    )
ORDER BY ff.fam_ID ASC, pp.plg_date DESC 
LIMIT 0, 30

Any insight in the issue is appreciated.

score 1 · Answer 1 · answered Nov 09 '12 at 08:51

The problem with the NOT IN solution is that, if the internal query has one or more null values, the result set is empty. You can try to solve this adding IS NOT NULL in the where clause of the internal query.

WHERE  ((DATEDIFF( NOW(), ppl.plg_date ) < 180) ) AND ppl.plg_date IS NOT NULL

score 0 · Answer 2 · answered Nov 09 '12 at 06:20

0

I would rephrase the query as below:

     SELECT ppl.plg_FamID
     FROM pledge_plg AS ppl
     HAVING MAX(pp.plg_date) < DATE_SUB( CURDATE(),INTERVAL 180 DAY);

answered Nov 09 '12 at 06:20

Yogendra Singh

32,067
6
59
71

MYSQL NOT IN subselect Query

2 Answers2