2

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

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

| improve this answer | |
  • I used the first option and it worked so fine . am really impressed about your skills . – humphrey Jun 24 '17 at 15:48
  • 2
    It's called practice. – joanolo Jun 24 '17 at 15:49
  • 1
    @Kondybas: no, not really. Double-check the results of all three queries. They're equivalent. – joanolo Jun 24 '17 at 17:08
  • 1
    I mean your description above the code – Kondybas Jun 24 '17 at 17:10
  • 1
    @Kondybas: I've changed my wording to make a little more clear. Thanks. – joanolo Jun 24 '17 at 17:12
3

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.

| improve this answer | |
  • 1
    SQL99 standard allow to use HAVING clause without GROUP BY under certain circumstances. MySQL at least v.5.6.36 conform it. – Kondybas Jun 24 '17 at 17:18
  • 1
    I reword myself, you're nominally right. You can have a HAVING without a GROUP BY, but I don't think this is what you want. HAVING is applied after the aggregation phase of execution of an SQL query... You need some aggregation to be performed, or otherwise it won't work/make sense. You have an implicit aggregation of the whole table when you don't have an explicit GROUP BY. It's the same case as SELECT count(*) FROM table HAVING count(*) > 0. There is aggregation, and you either get one row (with a count > 0) or no rows at all. This is not what I think you intend to do... – joanolo Jun 24 '17 at 17:25
  • Here is the quite good explanation: dev.mysql.com/doc/refman/5.6/en/group-by-handling.html – Kondybas Jun 24 '17 at 17:28
  • The right way to right this (LEFT JOIN / IS NULL) would be: 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 ; – ypercubeᵀᴹ Jun 24 '17 at 17:49
  • @ypercubeᵀᴹ Now I feel dumb. – Kondybas Jun 24 '17 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.