Reputation: 293
I have two tables with one to one relationship, the first is "book" and the second is "payment", I want to get the (cus_id) from "book"with the highest (price) from payment using join, which means i have to use max, but i can not get the right syntax for this
book has these columns (cus_id, inv_id as FK,....) and payment has (inv_id as PK, price,...)
I tried this syntax
select b.cus_id, p.price
from customer b, payment p
where b.inv_id=p.inv_id;
but this syntax absolutely won't give me the max price, and here i need the help.
Upvotes: 1
Views: 46
Reputation: 2072
Select a.cus_id,max(price)
from book a,payment
where a.inv_id = (Select b.inv_id from payment b where price = (select max(price) from payment))
Upvotes: 1
Reputation: 104
select b.cus_id, p.price from customer b, payment p where b.inv_id=p.inv_id AND p.price = (SELECT max(price) FROM payment WHERE inv = p.inv)
if you need only a specific book, add another filter for it
Upvotes: 0