Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2009
    Posts
    34

    Angry Unanswered: plz help me how to find that who pay the fee for this month

    Hi All
    i have big problem that how to find those who not pay the fee i have 2000 customers,i have register all the 2000 custome, 500 customer paid fee but the 1500 not paid,how to findthese 1500 customers who not pay the fee. plz help me i shall be very thank full


    thanks

  2. #2
    Join Date
    Sep 2009
    Posts
    44
    Depends on how your tables are set up, but it will look something like this:

    Code:
    select customer_id from customers
    where customer_id not in (select customer_id from fees_paid)
    Or, possibly:

    Code:
    select customer_id from customers
    except
    select customer_id from fees_paid
    The first might be more useful since you could add other fields easily, e.g. customer_name, that shouldn't be in fees_paid.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >but the 1500 not paid
    so you say.

    You provide us

    No Operating System name or version
    No Oracle version number (SELECT * from v$version).
    No error message (not your interpretation of the actual full and complete message).
    No complete SQL statement.

    No DDL for your table(s)
    No DML for test data
    No expected/desired results.
    Therefore no assistance for you is possible at this time.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    select customer_id from customers
    except
    select customer_id from fees_paid
    NOT valid syntax for Oracle!
    MINUS is valid
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2009
    Posts
    34

    Lightbulb plz help me how to find that who pay the fee for this month

    thanks for reply

    i use the first query it is working but i have another problem i.e when i use this

    select customer_id from customer where customer_id not in (select customer_id from fee_paid_customer where year=2008) ;


    this query works but it also show the customer who paid fee in 2007
    plz help me how to solve this problem


    in database i have two tables one is "customers" and the other is "fee_paid_customers"

    customer table contains information about the customers

    and the fee_paid_customer table contains information about the fee
    Last edited by oryazi; 12-10-09 at 05:51.

  6. #6
    Join Date
    Sep 2009
    Posts
    44
    Quote Originally Posted by anacedent View Post
    NOT valid syntax for Oracle!
    MINUS is valid
    At least as far as SQL92, EXCEPT or EXCEPT DISTINCT is the correct syntax, but, Oracle is simply wrong on that count. I don't use that construction much in practice and I'm a little surprised that 17 years after the fact they haven't fixed that.

    It's not like using MINUS instead of EXCEPT is impossible to fix without destroying backwards compatibility like, say, assuming that an empty string is NULL...

  7. #7
    Join Date
    Sep 2009
    Posts
    44
    Quote Originally Posted by oryazi View Post

    select customer_id from customer where customer_id not in (select customer_id from fee_paid_customer where year=2008) ;


    this query works but it also show the customer who paid fee in 2007
    plz help me how to solve this problem
    What's the problem? You asked for the customers except those who paid in 2008. Why do you think it should exclude people who paid in 2007 as well?

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    For 2000 customers, you could probably go through one by one and check. Should not take more than a day to do by hand. Without knowing what makes a paid customer, and what makes an unpaid customer, it is a little hard to figure out the query you need.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by oryazi View Post
    Hi All
    i have big problem that how to find those who not pay the fee i have 2000 customers,i have register all the 2000 custome, 500 customer paid fee but the 1500 not paid,how to findthese 1500 customers who not pay the fee. plz help me i shall be very thank full


    thanks


    I'll collect 10% if I show you
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Aug 2009
    Posts
    262
    elect customer_id from customer where customer_id in (select customer_id from fee_paid_customer where year=2008) ;

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >elect customer_id from customer where customer_id in (select customer_id from fee_paid_customer where year=2008) ;

    It bad/dangerous programming practice to use KEYWORD as column name.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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