Search code examples
mysqlsqlsubquery

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

  • SELECT MAX(date_created) FROM transactionInfo WHERE loan_id = 2;
  • SELECT * FROM transactionInfo WHERE date_created = (SELECT MAX(date_created) FROM transactionInfo)

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

Solution

  • 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.