Query Help Required

Discussion in 'Database Support' started by rehan.azher, Jun 2, 2009.

  1. rehan.azher

    rehan.azher Guppy

    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. Avataar

    Avataar Guppy

    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')

Share This Page

JodoHost - 26,000 hosting end-users in 100 countries
Plesk Web Hosting
VPS Hosting
H-Sphere Web Hosting
Other Services