Hello, I have a SQL query that I'm currently solving by doing two queries. I am wondering if there is a way to do it in a single query that makes it more efficient.
Consider two tables:
Transaction_Entries table and Transactions, each one defined below:
Transactions
- id
- reference_number (varchar)
Transaction_Entries
- id
- account_id
- transaction_id (references Transactions table)
Notes: There are multiple transaction entries per transaction. Some transactions are related, and will have the same reference_number string.
To get all transaction entries for Account X, then I would do
SELECT E.*, T.reference_number
FROM Transaction_Entries E
JOIN Transactions T ON (E.transaction_id=T.id)
where E.account_id = X
The next part is the hard part. I want to find all related transactions, regardless of the account id. First I make a list of all the unique reference numbers I found in the previous result set. Then for each one, I can query all the transactions that have that reference number. Assume that I hold all the rows from the previous query in PreviousResultSet
UniqueReferenceNumbers = GetUniqueReferenceNumbers(PreviousResultSet) // in Java
foreach R in UniqueReferenceNumbers // in Java
SELECT *
FROM Transaction_Entries
where transaction_id IN (SELECT *
FROM Transactions
WHERE reference_number=R
Any suggestions how I can put this into a single efficient query?
