title: Select query that iterates through the results of a first query
body: <p>This is the code we have at the moment that works fine as a select query:</p>
  
  <pre><code>SELECT DISTINCT
    Reference,
    (SELECT
      amount
    FROM tbl_DDTransactions
    WHERE DueDate = '2015-01-15'
    AND Reference = 'MAIN0134')
    AS LastMonth,
    (SELECT
      amount
    FROM tbl_DDTransactions
    WHERE DueDate = '2015-02-15'
    AND Reference = 'MAIN0134')
    AS CurrentMonth
  FROM tbl_DDTransactions
  WHERE Reference = 'MAIN0134'
  </code></pre>
  
  <p>The table we are pulling this information from can have any number of entries (each row actually relates to a transaction with the company reference as MAINxxxx).</p>
  
  <p>What we would like to do is gets a list a value of distinct MAIN references in the table, then have it loop through the code above, generating a row for each MAIN reference. We're not quite sure how to express this in SQL though. Any help appreciated.</p>