I’m having some issues performing a conditional join on 2 tables and would appreciate some advice.
Customer table, Loan table
I’m running the following SQL:
SELECT Customers., Loans.*
FROM Customers
JOIN Loans ON Customers.CustomerID = Loans.CustomerID
and
Customers.EndDate < Loans.Date
WHERE Loans.OutstandingBalance > 500;*
However, I’m only interested in the FIRST record from the Loans table which matches the condition. In the example attached, that means CustomerID=2, should join with Loan ID=2 only (and not Loan ID=3 or 4)
Perhaps, I can filter out other records at the end of the join, but is there a better way?
Thank you