Combining two-part SQL query into one query

Question

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, sum(debit_value) total 
  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 *, sum(debit_value) total 
       FROM Transaction_Entries 
       where transaction_id IN (SELECT * 
                                 FROM Transactions 
                                WHERE reference_number=R)
       AND account_id = X
       GROUP BY another_field

Any suggestions how I can put this into a single efficient query?

NOTE: I have edited the original question. The new addition is the fact that when I do the second query, I am only looking for Transaction Entries that match the reference_number AND have the same account Id. Also, I am trying to group by another_field and sum the debit_values according to that grouping.

What I am finding when trying to use the solution below provided by @Gratzy is that duplicate rows are being returned and so the sum(debit_value) is always twice the value it should be. I think it's because there are other Transaction_Entries in there that don't match the account_id but that match the join criteria.

Answer 1

Try

SELECT distinct E2.*, T.reference_number 
FROM Transaction_Entries E 
INNER JOIN Transactions T ON (E.transaction_id=T.id) 
INNER JOIN Transactions T2 on T.reference_number = T2.reference_number
INNER JOIN Transaction_Entries E2 on T2.id = E2.transaction_Id
where E.account_id = X

If the account has numerous transaction_Entries for the same reference_number you may get duplicates

EDIT Added @van's suggestion I believe he is correct, thank you.

EDIT This is edited to limit to the same account_id's

SELECT distinct E2.*, T.reference_number  
FROM Transaction_Entries E  
INNER JOIN Transactions T ON (E.transaction_id=T.id)  
INNER JOIN Transactions T2 on T.reference_number = T2.reference_number 
INNER JOIN Transaction_Entries E2 on T2.id = E2.transaction_Id and E2.account_id = E.account_id
where E.account_id = x 

Answer 2

I think this would work:

SELECT * 
FROM Transaction_Entries te
INNER JOIN Transactions t ON t.id = te.transaction_id
INNER JOIN (
  SELECT DISTINCT T.reference_number 
  FROM Transaction_Entries E 
  JOIN Transactions T ON (E.transaction_id=T.id) 
  WHERE E.account_id = X
) refs ON t.reference_number = refs.reference_number

Answer 3

why not:

select *
from   Transaction_Entries
where  transaction_id In (select   id, 
                          from     transactions 
                          group by reference_number)