This SQL statement will produce one row for each record found on the account_table for a given account number
We want also want to join the name and address table to the row. We only want one row from the transaction table (there are many)
that has the greatest transaction date and add it to our row as TransactionDate.
Here is how:
SELECT a.account_number, b.name_last, b.name_first, c.address, c.phonenumber, TransactionDate FROM account_table a LEFT OUTER JOIN ( SELECT MAX(transaction_date) AS TransactionDate FROM transaction_table GROUP BY transaction_number ) groupedtt ON a. account_number = groupedtt. account_number LEFT OUTER JOIN name_table b on a. account_number = b. account_number LEFT OUTER JOIN address_table c on a account_number = c. account_number where a. account_number = 'ACCT12345'