Results 1 to 5 of 5

Thread: Query

  1. #1
    Join Date
    Jan 2015
    Posts
    23

    Unanswered: Query

    Good day,
    Please I have been trying to make a query that compares the values in two differnebt fields and return result.
    I want to get the names of customers whose Total_sales is greater Balance or vice versa.
    Please help thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So what gave you tried?
    What does your table design look like?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2015
    Posts
    23
    I have Customer Table where the names are, I also have the Invoice Table where the Invoice_Total, Amount_Paid and Balance are. I have already joined both tables in a query call Customer_Invoice. I now want to filter this query (Customer_Invoice) to show me names and Balance of those whose Invoice_Total is avove (>) the Balance. What should the query look like and under what field should it be if am using the design view not SQL view? Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so how do you join the two tables, on that design there is nothing that associates a customer with an invoice.

    I'm assuming that a customer can have more than one invoice

    but lets assume you know how to create the query.. so its all down to the where clause

    Code:
    where invoice.total <> invoice.amount_paid

    but frankly this design looks flaky. one of the three columns is not needed...
    to derive the balnce you need to know the original amount (invoice_total) less payments (amount_paid).

    however I would expect payments to be in a separate table all together, as customers have a habit of paying according to their needs, so ytou may get one payment covering more than one invoice, and not neccesarily settling the full invoice amount. your current data model doesn't reflect that, and should you implement it you will loose visibility of what a customer has paid and when, against what outstanding amount.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2015
    Posts
    3

    Cool Try one of these methods

    1- the code below is the join type:

    SELECT customer.name, invoice.balance
    FROM CUSTOMER JOIN INVOICE ON CUSTOMER.customerID = INVOICE.customerID
    WHERE invoice.Total > invoice.balance ;


    2- this one does not need to join two tables, you just need to select distinct from the two tables where the primary and foreign key condition meet.

    SELECT DISTINCT customer.name, invoice.balance
    FROM customer, invoice
    WHERE customer.customerID = invoice.customerID AND invoice.Total > invoice.balance ;

Posting Permissions

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