Databases
StackOverflow2013
Postsdbo
POCombining two-part SQL query into one query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCombining two-part SQL query into one query
    primarykey
    data
    text
    <p>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.</p> <p>Consider two tables:</p> <p>Transaction_Entries table and Transactions, each one defined below:</p> <pre><code>Transactions - id - reference_number (varchar) Transaction_Entries - id - account_id - transaction_id (references Transactions table) </code></pre> <p>Notes: There are multiple transaction entries per transaction. Some transactions are related, and will have the same reference_number string.</p> <p>To get all transaction entries for Account X, then I would do</p> <pre><code>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 </code></pre> <p>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 <code>PreviousResultSet</code></p> <pre><code>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 </code></pre> <p>Any suggestions how I can put this into a single efficient query?</p> <p>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.</p> <p>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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UScambo
    1. UScambo
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCombining two-part SQL query into one query
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POCombining two-part SQL query into one query
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODatabase and version?
      singulars
      1. POCombining two-part SQL query into one query
      1. USMark Byers
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload