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

what have you tried so far?
where has that gone wrong?
what steps did you take to see if you could resolve the problem?

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?

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;