How to select only members who have not made any deposits this month in mysql

Question

I am fetching data from two tables, members and fees .I would like to select only those members who have not made any deposit this month. This is what I have tried but the results are not what I am intending to get . any help will be appreciated . I am using mysql .

SELECT
    members.memberid, 
    members.companyid, 
    members.name, 
    members.initials, 
    members.surname, 
    members.datejoined,
    members.usercode, 
    members.address, 
    members.cell, 
    members.pic, 
    members.idnumber, members.rank, 
    members.address2,
    fees.feestype,fees.amountpaid,
    fees.companyid,fees.paiddate,
    fees.usercode
FROM 
    members
    INNER JOIN fees
        ON members.memberid = fees.memberid
WHERE 
        MONTH(fees.paiddate) < MONTH(CURDATE())
    AND YEAR(fees.paiddate)  < YEAR(CURDATE())

joanolo · Accepted Answer · 2017-06-25T08:44:09.223

You need to do it in a diffent way: choose all members that are not IN the set of members who paid something this month:

SELECT
    memberid, name  /* , and anything you need */
FROM
    members
    /* JOIN fees ON fees.memberid = members.memberid */
WHERE
    memberid NOT IN
    (
    /* members who paid something this month */ 
    SELECT 
        memberid
    FROM
        fees
    WHERE
        YEAR(paiddate)  = YEAR(curdate())  AND
        MONTH(paiddate) = MONTH(curdate())
    ) ;

dbfiddle here

You actual query specified members who paid something in the years previous to the current one, and month previous to the current one.

As a second alternative, you can use NOT EXISTS:

SELECT
    memberid, name  /* , and anything you need */
FROM
    members
    /* JOIN fees ON fees.memberid = members.memberid */
WHERE
    NOT EXISTS
    (
    /* any payment from this specific member this month */ 
    SELECT 
        1
    FROM
        fees
    WHERE
        fees.memberid = members.memberid   AND
        YEAR(paiddate)  = YEAR(curdate())  AND
        MONTH(paiddate) = MONTH(curdate())
    ) ;

dbfiddle here

And yet another (not so clear, in my opinion) alternative, do a LEFT JOIN and set a WHERE condition that will filter out the rows actually retrieving something from the right side of the JOIN (i.e.: using only the rows WHERE all columns, and specifically memberid are NULL on the right side):

SELECT
    members.memberid, members.name  /* , and anything you need */
FROM
    members
    /* JOIN fees ON fees.memberid = members.memberid */
    LEFT JOIN /* Imperative to be LEFT JOIN */
    (
    /* members who paid this month */ 
    SELECT 
        fees.memberid
    FROM
        fees
    WHERE
        YEAR(paiddate)  = YEAR(curdate())  AND
        MONTH(paiddate) = MONTH(curdate())
    ) AS s0 
    ON s0.memberid = members.memberid
WHERE
    s0.memberid IS NULL /* The LEFT JOIN produced nothing on the right side */

Check Best practice between using LEFT JOIN or NOT EXISTS

And check all dbfiddle here

This is called, in relational algebra, an antijoin

UPDATE

Taking a step further from Kondybas's solution, and as pointed out in the comments to Kondibas answer by ypercube, this can be further simplified to:

SELECT
    members.memberid, members.name  /* , and anything you need */
FROM
    members
    /* JOIN fees f1 ON fees.memberid = members.memberid */
    LEFT JOIN fees f2 ON 
        f2.memberid = members.memberid AND
        f2.paiddate BETWEEN date_format(curdate() ,'%Y-%m-01') AND curdate()
WHERE
    f2.memberid IS NULL ;

Writing the condition like this (i.e.: not having a function applied to paiddate), it will use an index such as:

CREATE INDEX idx_member_date ON fees (memberid, paiddate);

... as can be seen on the execution plan:

id | select_type | table   | type | possible_keys   | key             | key_len | ref                                          | rows | Extra                   
-: | :---------- | :------ | :--- | :-------------- | :-------------- | :------ | :------------------------------------------- | ---: | :-----------------------
 1 | SIMPLE      | members | ALL  | null            | null            | null    | null                                         |    2 |                         
 1 | SIMPLE      | fees    | ref  | idx_member_date | idx_member_date | 5       | fiddle_ZCXLEXEURTHEWEPFKPOV.members.memberid |    1 | Using where; Using index

dbfiddle here

Kondybas · Answer 2 · 2017-06-24T18:00:34.757

The third alternative to the joanolo's solutions is to fetch records with NULLs from LEFT JOIN of tables:

SELECT w.id
  FROM members AS w
  LEFT JOIN fees AS z ON w.memberid = z.memberid
 WHERE DATE_FORMAT(z.paiddate, '%Y%M') = DATE_FORMAT(NOW(), '%Y%M')
    OR z.paiddate IS NULL
HAVING z.paiddate IS NULL
;

As yupecube suggested, query can be simplified to:

SELECT w.id
  FROM members AS w
  LEFT JOIN fees AS z
         ON w.memberid = z.memberid 
        AND DATE_FORMAT(z.paiddate, '%Y%M') = DATE_FORMAT(NOW(), '%Y%M')
 WHERE z.paiddate IS NULL
;

The only disadvantage is that JOINs on complex functional conditions are in general very slow.

How to select only members who have not made any deposits this month in mysql

2 Answers2