Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    5

    Unanswered: Min/max problem - Need help

    Hi,
    I have a problem related to the function min and max. In this case Im working with 5 tables with this relation:

    1. Customer(Name, address, KNR(PK)
    2. Belong(KNR(FK), BENR(FK)
    3. Order(order_sum, BENR(PK)
    4. IN_Order(price, order_amount, order_date, BENR(FK), BANR(FK)
    5. Product (Product_name, product_price, BANR(PK)

    I want to answar this question:

    - Which customer(s) (show the customer(s), have the highest order sum and which have the lowest order sum. I should also show the product in the order.


    If I have been unclear so feel free to ask

    thanks in advance!

    /Martin

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what have you tried so far?
    where has that gone wrong?
    what steps did you take to see if you could resolve the problem?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2011
    Posts
    5

    reply:::

    My code:

    Select name, order_sum, product_name from customer c, belong b, order o, in_order i, product p WHERE order_sum=(select min(order_sum), max(order_sum) from order) AND p.banr=i.banr AND i.benr=o.benr AND o.benr=b.benr AND b.knr=c.knr;

    The result of the code above:
    - operand should contain i column(s)

    When I only use max() function i get the right result of max.
    How should I use both max and min function in the code?

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Try the following:

    Select name, order_sum, product_name
    from customer c,
    belong b,
    order o,
    in_order i,
    product p,
    (select min(order_sum) minsum, max(order_sum) maxsum FROM order) x
    WHERE (order_sum = x.minsum OR order_sum = x.maxsum)
    AND p.banr=i.banr
    AND i.benr=o.benr
    AND o.benr=b.benr
    AND b.knr=c.knr;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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