Query Help Required

[rehan.azher]

Hi All,

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​

2. payments

payments.Amount,
payments.PaymentMonth,
payments.PaymentDate,
payments.CustomerID,
payments.PaymentYear,
payments.CustomerAReaID,
payments.ReceiptNo,
payments.USerID​

3. area

area.AreaID,
area.AreaDesc​

4. tblctype

tblctype.CtypeID,
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:

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.

 

[Avataar]

SELECT
customer.CustomerName,
customer.CustomerHouseNo,
area.AreaDesc,
tblctype.Ctype
FROM
customer
INNER JOIN area ON (area.AreaID=customer.AreaID)
INNER JOIN tblctype ON (customer.CtypeID=tblctype.CtypeID)
WHERE
(customer.CustomerID Not IN (Select CustomerId From payments Where payments.PaymentDate BETWEEN '2009-04-01' AND '2009-04-31') )
AND
(customer.AreaID = 'BM')