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.

For that, first, alias your main query tables and use a WHERE NOT EXISTS sub-query.
This allows you to refer to the main query (via the aliases) from the sub-query:

SELECT
customer.CustomerName,
customer.CustomerHouseNo,
area.AreaDesc,
tblctype.Ctype
FROM
customer cu
INNER JOIN payments pa ON (cu.CustomerID=pa.CustomerID)
INNER JOIN area ar ON (ar.AreaID=cu.AreaID)
INNER JOIN tblctype ct ON (cu.CtypeID=ct.CtypeID)
WHERE (cu.AreaID = 'BM')
and not exists (
  select 1
  from customer cu1
  inner join payments pa1 on cu1.CustomerID=pa1.CustomerId
  where cu1.CustomerId=cu.CustomerId  --Here, we match with the main query
  and pa.PaymentDate between '2009-04-01' and '2009-04-31'
)

Hi All,

thanks to all who read it and looked out for a solution. I have achieved my required result using following method.

First I created a view of all customers who have paid for the current month using following definition:

DROP VIEW IF EXISTS `citycable`.`cmpaid`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW  `citycable`.`cmpaid` AS select `customer`.`CustomerName` AS `CustomerName`,`customer`.`CustomerHouseNo` AS `CustomerHouseNo`,`area`.`AreaID` AS `AreaID`,`tblctype`.`CtypeID` AS `CtypeID`,`customer`.`CustomerID` AS `CustomerID` from (((`customer` join `payments` on((`customer`.`CustomerID` = `payments`.`CustomerID`))) join `area` on((`area`.`AreaID` = `customer`.`AreaID`))) join `tblctype` on((`customer`.`CtypeID` = `tblctype`.`CtypeID`))) where ((`payments`.`PaymentMonth` = month(cast(now() as date))) and (`payments`.`PaymentYear` = year(cast(now() as date))));

After this I use LEFT JOIN (similar to MINUS) to extract the records which exist in my customer table but no it cmpaid(View). My query looks like as following:

SELECT 
  customer.CustomerName,
  customer.CustomerHouseNo
FROM
 customer
 LEFT OUTER JOIN cmpaid ON (customer.CustomerID=cmpaid.CustomerID)
WHERE
  (cmpaid.customerID IS NULL) AND 
  (customer.AreaID = 'BM')

I am using AreaID to extract for a particular area.


Thanks again

It works too, but is heavier on the server.
A NOT EXISTS is usually lighter and faster than a LEFT OUTER JOIN.