SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query Help Required

    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:

    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.

  2. #2
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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:

    Code:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •