rehan.azher
Guppy
Hi All,
I have four different tables in my database with following structure:
//===================================================================
1. customer
2. payments
3. area
4. tblctype
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:
OR I can have the result using following query:
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.
I have four different tables in my database with following structure:
//===================================================================
1. customer
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
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
2. payments
payments.Amount,
payments.PaymentMonth,
payments.PaymentDate,
payments.CustomerID,
payments.PaymentYear,
payments.CustomerAReaID,
payments.ReceiptNo,
payments.USerID
payments.PaymentMonth,
payments.PaymentDate,
payments.CustomerID,
payments.PaymentYear,
payments.CustomerAReaID,
payments.ReceiptNo,
payments.USerID
3. area
area.AreaID,
area.AreaDesc
area.AreaDesc
4. tblctype
tblctype.CtypeID,
tblctype.Ctype
//===============================================================tblctype.Ctype
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:
Code:
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:
Code:
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.