Need Help with an SQL query
Hi Guys,
I am having trouble with an sql query and I can't figure it out how to get it right. I have 6 tables with the following schemas in the database:
Borrower (customer_name, loan_number)
Loan (branch_name, loan_number, amount)
Branch (branch_name, branch_city, assets)
Customer (customer_name, customer_street, customer_city)
Account (branch_name, account_number, balance)
Depositor(customer-name, account-number)
and I need to know "who borrow the most money in each branch"
For this query I am only using 2 tables (Loan and Borrower)
I have the following
select Customer_Name, Branch_Name
-> from Borrower B, Loan L
-> where B.Loan_Number = L.Loan_Number
-> group by Branch_Name
-> having (select max(Amount) from Loan);
but I get the wrong output....
... I will appreciate any help provided in this forum. Thank you very much.
Cesar
[1005 byte] By [
cez_master] at [2007-11-11 7:39:00]
# 1 Re: Need Help with an SQL query
Try this mate:
SELECT Customer_Name, Branch_Name
FROM Borrower B,
INNER JOIN Loan L
ON B.Loan_Number = L.Loan_Number
INNER JOIN
( SELECT branch_name, MAX(amount) AS amount
FROM Loan
GROUP BY branch_name
) Lmax
ON L.branch_name = Lmax.branch_name
AND L.amount = Lmax.amount
hth,
Cheers,
Paul
# 2 Re: Need Help with an SQL query
Hi Paul,
Thanks alot for the help..
I wasn't so sure what INNER JOIN was but I find out that it is used just like the comma (,) to combine the tables and the ON keyword specifies the condition.
I am not sure what the Lmax means after the parentheses..... can you please explain..
Thank you
Cesar
# 3 Re: Need Help with an SQL query
G'day...
Sure mate - this part:
INNER JOIN
( SELECT branch_name, MAX(amount) AS amount
FROM Loan
GROUP BY branch_name
) LMax
Is using the SELECT statement - fully enclosed by brackets - just as if it was an actual table, or a view. (These are known as "inline views" ). the "LMax" after the brackets just gives that select statement an alias/name, so it can be referred to in other places, for instance on the join predicate or the select list...
hth,
Cheers,
Paul
