Hi All,
I have four different tables in my database with following structure:
//===================================================================
- customer
[INDENT]customer.CustomerName,
customer.CustomerHouseNo,
customer.AreaID,
customer.CustomerID,
customer.CustomerType,
customer.CustomerBalance,
customer.CustomerAdvance,
customer.CustomerMonthlyFee,
customer.Remarks,
customer.CustomerConnectionDate,
customer.CustomerStatus,
customer.CustomerTelephone,
customer.EUserID,
customer.USerID,
customer.EditDate,
customer.CDDate,
customer.DisRemarks,
customer.CtypeID,
customer.CIP,
customer.CMAC[/INDENT]
- payments
[INDENT]payments.Amount,
payments.PaymentMonth,
payments.PaymentDate,
payments.CustomerID,
payments.PaymentYear,
payments.CustomerAReaID,
payments.ReceiptNo,
payments.USerID[/INDENT]
- area
[INDENT]area.AreaID,
area.AreaDesc[/INDENT]
- tblctype
[INDENT]tblctype.CtypeID,
tblctype.Ctype
[/INDENT]//===============================================================
payments table holds customers monthly payments records with payment date, month and year.
I want to retrieve the records for all those customers which have not paid in a particular month.
For the customers that have paid in particular month i mad following query and can have desired result:
SELECT
customer.CustomerName,
customer.CustomerHouseNo,
area.AreaDesc,
tblctype.Ctype
FROM
customer
INNER JOIN payments ON (customer.CustomerID=payments.CustomerID)
INNER JOIN area ON (area.AreaID=customer.AreaID)
INNER JOIN tblctype ON (customer.CtypeID=tblctype.CtypeID)
WHERE
(payments.PaymentDate BETWEEN '2009-04-01' AND '2009-04-31') AND
(customer.AreaID = 'BM')
OR I can have the result using following query:
SELECT
customer.CustomerName,
customer.CustomerHouseNo,
area.AreaDesc,
tblctype.Ctype
FROM
customer
INNER JOIN payments ON (customer.CustomerID=payments.CustomerID)
INNER JOIN area ON (area.AreaID=customer.AreaID)
INNER JOIN tblctype ON (customer.CtypeID=tblctype.CtypeID)
WHERE
(customer.AreaID = 'BM') AND
(payments.PaymentMonth = 4) AND
(payments.PaymentYear = 2009)
the result of above both queries are identical (Customers who made payment in April 2004).
Now problem is i am unable to set a query design that can give me the customer that have not paid in month of April 2009.
I hope I have made enough effort to clear my query and am posting in the right forum.
Thanks for all who read as well as replied.