MySQL – How to Select Members Without Deposits This Month

MySQL

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())

Best Answer

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

Related Question