title: SQL Server - Select from child-parent-child and return multiple results-set
body: <p>I am using SQL Server 12/Azure and have 3 tables (T1, T2, T3) where T1 has 1-many with T2 and T3, I want to select from T2 and return the information of T1 records and their associated T3 records.
  To give a simplified example, T1 is "Customer", T1 is "Orders", T3 is "CustomerAddresses", so a customer can have many orders and multiple addresses. Now I want to query the orders and include the customers information and addresses, to make things a little bit complicated, the query for orders could include matching on the customer addresses, e.g. get the orders for these addresses.</p>
  
  <pre><code>Customer Table                   
  ----------------------          
  Id, Name,...                    
  ----------------------          
  
  Orders Table                            
  ------------------------------          
  OrderId, CustomerKey, Date,...          
  ------------------------------          
  
  CustomerAddresses
  -----------------------------------------------
  AutoNumber, CustomerKey, Street, ZipCode,...
  -----------------------------------------------
  </code></pre>
  
  <p>I am having trouble writing the best way (optimized) to return all the results in one transaction and dynamically generate the sql statements, this is how I think the results should come back:</p>
  
  <p>Orders (T2) and customer information (T1) are returned in one result-set/table and CustomerAddresses (T2) are returned in another result-set/table. I am using ADO.NET to generate and execute the queries and use System.Data.SqlClient.SqlDataReader to loop on the returned results.</p>
  
  <p>Example of how the results could come back: </p>
  
  <pre><code>Order-Customer Table
  -------------------------------
  Order.OrderId, Customer.Id, Customer.Name, Order.Date,....
  -------------------------------
  
  CustomerAddresses
  -------------------------------
  AutoNumber, CustomerKey, Street
  -------------------------------
  </code></pre>
  
  <p>This is an example of a query that I currently generate:</p>
  
  <pre><code>SELECT [Order].[OrderId], [Order].[Date], [Customer].[Id], [Customer].[Name] 
  FROM Order 
  INNER JOIN [Customer] on [Order].[CustomerKey] = [Customer].[Id] 
  WHERE ([Order].[Date] &gt; '2015-06-28') 
  </code></pre>
  
  <p>Questions:
  1. How do I extend the above query to also allow returning the CustomerAddresses in a separate result-set/table?
  To enable matching on the CustomerAddresses I should be able to do a join with the Customer table and include whatever columns I need to match in the WHERE statement.</p>
  
  <ol start="2">
  <li>Is there a better, simpler and more optimized way to achieve what I want?</li>
  </ol>
  
  <p>-------- UPDATE ---------------
  To clarify more about how I use the returned data in my app:</p>
  
  <ol>
  <li><p>I am using ADO.NET, SqlConnection, SqlCommand and SqlDataReader to parse the results. (I don't want to use Entity Framework or any other high-level DB Framework here)</p></li>
  <li><p>My model object is a collection of T2 (Orders) which contains T1 (Customer information) and T3 (CustomerAddresses) </p></li>
  </ol>
  
  <p><strong>OrderClass:</strong> 
  OrderId, OrderDate, CustomerId, CustomerName, CustomerAddresses[],...</p>
  
  <p><strong>CustomerAddresses Class:</strong> 
  Street, ZipCode, ....</p>
  
  <p>I found out that people usually return all the results within a single select statement in one table which returns a redundant data. What I prefer is to return the tables as is (T1, T2 and T3) that only contains the relevant information then I can process it in my app to create the model.</p>
  
  <p>Another solution is to insert the IDs from the select statement into a Temp table then return the results in multiple select statements:</p>
  
  <pre><code>Select T1.* From T1 where  Id in (
          select Temp.T1Id from Temp )
  Select T2.* From T2 where  Id in (
          select Temp.T2Id from Temp )
  Select T3.* From T3 where  Id in (
          select Temp.T3Id from Temp )
  </code></pre>