title: Using multiple resultsets combined with joins in T-SQL
body: <p>I'm currently using joins inside my stored procedures for outputting elements from different tables. An aggressive example</p>
  
  <pre><code>select a.*, b.*, c.*, d.*, e.*, f.* from tableA a
  join tableB b on a.id = b.foreignid
  join tableC c on b.id = c.foreignid
  join tableD d on c.id = d.foreignid
  join tableE e on d.id = e.foreignid
  join tableF f on e.id = f.foreignid
  where a.id = 1
  </code></pre>
  
  <p>It's getting pretty unhandy to work with when mapping the output to entities in my C# code, since I have to maintain a lot of boilerplate code.
  Instead I would look into using multiple resultsets, so that I could map each resultset into an object type in code.
  But how would I go around achieving this when I my case the different results would all relate to each other? The examples I've been able to find all revolved around selecting from different tables where the data were not related by foreign keys like mine. If I were to ouput my result in multiple resultsets the only thing I can come up with is something like this</p>
  
  <pre><code>select a.* from tableA
  where a.id = 1
  
  select b.* from tableB
  join tableA a on a.id = b.foreignid
  where a.id = 1
  
  select c.* from tableC
  join tableB b on b.id = c.foreignid
  join tableA on a.id = b.foreginid
  where a.id = 1
  
  select d.* from tableD
  join tableC c on c.id = d.foreignid
  join tableB b on b.id = c.foreignid
  join tableA a on a.id = b.foreignid
  where a.id = 1
  
  select e.* from tableE
  join tableD d on d.id = e.foreignid
  join tableC c on c.id = d.foreignid
  join tableB b on b.id = c.foreignid
  join tableA a on a.id = b.foreignid
  where a.id = 1    
  
  select f.* from tableF
  join tableE e on e.id = f.foreignid
  join tableD d on d.id = e.foreignid
  join tableC c on c.id = d.foreignid
  join tableB b on b.id = c.foreignid
  join tableA a on a.id = b.foreignid
  where a.id = 1    
  </code></pre>
  
  <p>But this is not cleaner, a lot more ineffecient (I would suppose, since there's alot more join statements)
  Is it possible to use multiple resultset in this way I'm trying to? I just don't know how I would write the sql statements in the stored proc without having to do massive joins per resultset as in the example. And with the current solution I get an explosion of columns since I join them all together</p>