CODEWITHSUNDEEP
mysqlsqlsubquery
sukaretto
sukaretto

Reputation: 69

SQL Query to get the latest transaction date for a foreign key

The title is a bit confusing but is actually accurate about what I am asking for so please bear with me as I try to explain this as concise as I could.

Basically in my mySql server, I have three tables: customerInfo, loanInfo, and transactionInfo (this is some sort of a loan system).

the loanInfo amd customerInfo has their respective primary keys connected to the transactionInfo as its foreign key, creating a relationship between the three tables. (I thought it would be important to explain how the db works for you guys)

I need a query that will give me the maximum value of date from the transactionTable where the loanID is set to a specific value therefore giving me the transaction record with the latest date among all the other transaction for a specific loan

I really hope I made this clear.

Here are some of the queries that I have tried which returned no result

EDIT for D-Shih

Here is a sample datatransaction table sample

I want to get the transaction with the latest date where the loanID = 2, so the query should return the transaction with 4/14/2019 as the date

Upvotes: 0

Views: 962

Answers (1)

Maverick Fabroa
Maverick Fabroa

Reputation: 1173

Try using ORDER BY descending and set LIMIT if required.

SELECT * FROM transactionInfo ORDER BY date_created DESC;

You can also add WHERE clause:

SELECT * FROM transactionInfo WHERE loan_id = 2 ORDER BY date_created DESC;

This should get the latest transaction info according to the date_created.

Upvotes: 1

Related Questions