mysql - Trouble with having clause -

my tables, borrower:

+----------+---------+ | name     | loan_id | +----------+---------+ | adams    | l16     | | curry    | l93     | | hayes    | l15     | | jackson  | l14     | | jones    | l17     | | smith    | l11     | | smith    | l23     | | williams | l17     | +----------+---------+ 

loan:

+---------+-------------+--------+ | loan_id | branch_name | amount | +---------+-------------+--------+ | l11     | round hill  |    900 | | l14     | downtown    |   1500 | | l15     | perryridge  |   1500 | | l16     | perryridge  |   1300 | | l17     | downtown    |   1000 | | l23     | redwood     |   2000 | | l93     | mianus      |    500 | +---------+-------------+--------+ 

i wish find maximum loan taken customer.

i partially successful query:

select name, max(amount)  loan, borrower  borrower.loan_id=loan.loan_id; 

result:

+-------+-------------+ | name  | max(amount) | +-------+-------------+ | adams |        2000 | +-------+-------------+ 

but taking name first row , finding maximum amount , printing result. used having clause this:

select name, amount loan, borrower  borrower.loan_id=loan.loan_id  having max(amount)=amount; 

but empty set.

you can group by customer , max amount joined loan table.

this query similar first attempt, difference in group by clause. causes query return 1 row per customer name (name in group by). max (and sum too, in example) calculated per 'group', per name in case.

in query, didn't have group by, max determined (aggregated) on rows. many other databases wouldn't execute query, because feel field should either specified in group by or should called in aggregation function min, max, or sum. mysql bit silly different, because picks of customer names display.

select   b.name,   max(l.amount) highest,   sum(l.amount) total   borrower b   inner join loan l on l.loan_id = b.loan_id group   b.name 

of course, better if customer has id. after all, you're 2 customers both named smith.