title: Finding the T-SQL to return these values
body: <p>I'm trying to write a stored procedure that will return two calculated values for each record according to the rules below, but I haven't figured out how to structure the SQL to make it happen. I'm using SQL Server 2008.</p>
  
  <p>First, the relevant tables, and the fields that matter to the problem.</p>
  
  <p>ProductionRuns</p>
  
  <pre><code> RunID (key, and RunID is given to the stored proc as its parameter)
   ContainerName
   ProductName
   TemplateID
  </code></pre>
  
  <p>TemplateMeasurements</p>
  
  <pre><code> MeasurementTypeID
   TemplateID
  </code></pre>
  
  <p>SimpleBounds</p>
  
  <pre><code> MeasurementTypeID
   TemplateID
   UpperBound
   LowerBound
  </code></pre>
  
  <p>ContainerBounds</p>
  
  <pre><code> MeasurementTypeID
   TemplateID
   UpperBound
   LowerBound
   ContainerName
  </code></pre>
  
  <p>ProductBounds</p>
  
  <pre><code> MeasurementTypeID
   TemplateID
   UpperBound
   LowerBound
   ProductName
  </code></pre>
  
  <p>And this is what I'm trying to return. I want to return a calculated upper bound and lower bound value for each TemplateMeasurements record that has a matching TemplateID with the ProductionRuns record that has the supplied runID.</p>
  
  <p>The calculated upper and lower bounds basically get the tightest bound that can be obtained as a result of the simple, container and product bounds, if they qualify.</p>
  
  <p>If a SimpleBounds record exists with the correct MeasurementTypeID and TemplateID, then that becomes one of the qualifying bounds for a particular MeasurementTypeID and record of TemplateMeasurements.</p>
  
  <p>For a ContainerBound record to qualify, the TemplateID and MeasurementTypeID must match, but also the ContainerName must match the value for ContainerName in the ProductionRuns record. Also for ProductBounds, the same is true, but for ProductName.</p>
  
  <p>For a particular MeasurementTypeID, take all the qualifying bounds, and find the least Upper Bound, and that will be the calculated Upper Bound that is to be returned. Find the greatest Lower Bound of the qualifiers and that will be the returned Lower Bound.</p>
  
  <p>I have no idea how to put together SQL to do this however.</p>
  
  <p>Also, if none of the three bound tables qualify for a particular MeasurementTypeID, then null could be returned.</p>
  
  <p>My thought would be some kind of left outer join, but I'm not sure how to extend that to three tables that could all have null in the results.</p>
  
  <p>Thanks for the help.</p>