SQL Query Help Required

[rehanazher]

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.

For the customers that have paid in particular month i made following query and can have desired result:

[B]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')
[/B]

OR I can have the result using following query:

[B]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)[/B]

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.

[Mytheral]

Only way I can think of doing it is by nesting two SQL statements together. (But I'm horrible at that)

Getting a list of customers that have paid in april 2009 then requesting a full list of customers and filtering out the ones that appear on the first list. Perhaps someone with better SQL skills can elaborate.

[rehanazher]

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