title: Joining a table value function to a MSSQL query
body: <p>I have a table-value function that takes an ID number of a person and returns a few rows and columns. In another query, I am creating a SELECT that retrieves a lot of information about many people. How can I pass an id number from my main query to my function to sum a column and join it to my main query? I wish I didn't have a table value function since that would work easily, however, this function is used elsewhere and I'd like to reuse it. Perhaps this isn't even possible with a table-value function and I need to create a scalar one.</p>
  
  <p>My main Query looks like this:</p>
  
  <pre><code>select id_num, name, balance 
  from listOfPeople
  </code></pre>
  
  <p>And the table-value function looks like this:</p>
  
  <pre><code>calculatePersonalDiscount(id_number)
  </code></pre>
  
  <p>I would like to do something like:</p>
  
  <pre><code>select id_num, name, balance 
  from listOfPeople
  left join 
  (
    SELECT id_num, SUM(discount)
    FROM calculatePersonalDiscount(listOfPeople.id_num)
  ) x ON x.id_num = listOfPeople.id_num
  </code></pre>
  
  <p>But you can't pass listOfPeople.id_num into the function since it's not really the same scope.</p>