title: How can I join on a stored procedure?
body: <p>I have a stored procedure that takes no parameters, and it returns two fields. The stored procedure sums up all transactions that are applied to a tenant, and it returns the balance and the id of the tenant.</p>
  
  <p>I want to use the record set it returns with a query, and I need to join it's results on the id of the tenant.</p>
  
  <p>This is my current query:</p>
  
  <pre><code>SELECT t.TenantName, t.CarPlateNumber, t.CarColor, t.Sex, t.SSNO, t.Phone, t.Memo,
          u.UnitNumber,
          p.PropertyName
  FROM tblTenant t
      LEFT JOIN tblRentalUnit u
      ON t.UnitID = u.ID
  
      LEFT JOIN tblProperty p
      ON u.PropertyID = p.ID
  
  ORDER BY p.PropertyName, t.CarPlateNumber
  </code></pre>
  
  <p>The stored procedure is this:</p>
  
  <pre><code>SELECT tenant.ID AS TenantID, SUM(ISNULL(trans.Amount,0)) AS TenantBalance FROM tblTenant tenant
      LEFT JOIN tblTransaction trans
      ON tenant.ID = trans.TenantID
      GROUP BY tenant.ID
  </code></pre>
  
  <p>I would like to add the balance from the stored procedure to it also.</p>
  
  <p>How can I do this?</p>