How to Check Payment Month wise

Question

I am creating a simple gim system payment reminder calculation part. I want to get the result of those who are not paid the amount of the current month whose people name need to be displayed. What I tried so far I attached below.

Member table

id   mno    pname       dob 
1   e001   john     1991.12.3
2   e002   steve     1987.11.3
3   e003   sunm     1986.11.31
4   e004   jeba      1981.1.3

Payment table

id   mno     paydate       amount     months
1   e001   2020-12-14      20000       12

Months table 12 means - December month I got the SQL code of current month MONTH(getdate())

I tried query like this

  SELECT Member.mno
    ,Member.pname
    ,payment.amount
FROM Member
LEFT JOIN payment ON Member.mno = payment.mno
WHERE payment.amount IS NULL
    AND payment.months = MONTH(getdate())

When I run the query I didn't get any result. I want to get the result of those who are not paid the amount of the current month whose people name need to be displayed. What was the problem with the above query?

Your question is tagged "mysql", but the code will not run in MySQL. — Gordon Linoff, Dec 21 '20 at 11:56

score 1 · Answer 1 · answered Dec 21 '20 at 11:35

1

You need to move your condition from WHERE clause to ON clause as follows:

SELECT MEMBER.MNO,
       MEMBER.PNAME,
       PAYMENT.AMOUNT
  FROM MEMBER LEFT JOIN PAYMENT ON MEMBER.MNO = PAYMENT.MNO
                                AND PAYMENT.MONTHS = MONTH(GETDATE())
 WHERE PAYMENT.AMOUNT IS NULL

answered Dec 21 '20 at 11:35

Popeye

35,427
4
10
31

thanks sir. how to check paid people names – creative one2018 Dec 21 '20 at 11:40
` WHERE PAYMENT.AMOUNT IS NULL` --> ` WHERE PAYMENT.AMOUNT IS NOT NULL` ------- OR convert your query to use INNER JOIN and omit the WHERE clause – Popeye Dec 21 '20 at 11:41
thanks........... sir. each month will this work successfully MONTH(GETDATE()) it will automattly changed month and check the payment – creative one2018 Dec 21 '20 at 11:43
Yes, it will work for each month – Popeye Dec 21 '20 at 11:44

How to Check Payment Month wise

1 Answers1