CODEWITHSUNDEEP
mysqljoin
beshr
beshr

Reputation: 293

join tables with using max mysql

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

Answers (2)

Kshitij Kulshrestha
Kshitij Kulshrestha

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

Nikko
Nikko

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

Related Questions