Trouble with having clause

Question

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 to find the maximum loan taken by the customer.

I was partially successful with the query:

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

Result:

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

But it is taking the name from the first row and finding the maximum amount and printing the result. So I used the having clause like this:

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

But I get an empty set.

Answer 1

You can group by customer and get the max amount from the joined loan table.

This query is very similar to your first attempt, but the difference is in the group by clause. This causes the query to return one row per customer name (name is in group by). The max (and sum too, in my example) are calculated per 'group', so per name in this case.

In your query, you didn't have group by, so the max is determined (aggregated) over all rows. Many other databases wouldn't even execute your query, because they feel a field should either be specified in group by or it should be called in an aggregation function like min, max, or sum. MySQL is a bit silly different, because it just picks any of the customer names to display.

select
  b.name,
  max(l.amount) as Highest,
  sum(l.amount) as Total
from
  Borrower b
  inner join Loan l on l.loan_id = b.loan_id
group by
  b.Name

Of course, it would be better if a customer also has an ID. After all, you're likely to get two customers both named Smith.

Answer 2

If all you want is to get the loan with the maximum amount and the customer who has it, then you can use the following query:

SELECT b.name, l.loan_id, l.amount 
FROM borrower AS b
INNER JOIN loan AS l ON b.loan_id = l.loan_id
ORDER BY l.amount DESC
LIMIT 1

The query creates a set of all loans along with their associated customer, orders this set by amount in descending order and returns the topmost record, i.e. the record having the maximum amount.

If more than one loans have the same maximum amount and you want the data of all of these loans returned, then you can use the following query:

SELECT b.name, l.loan_id, l.amount 
FROM borrower AS b
INNER JOIN loan AS l ON b.loan_id = l.loan_id
WHERE l.amount IN (SELECT MAX(amount)
                   FROM loan )

Demo here