dimanche 28 juin 2015

SQL Server - Select from child-parent-child and return multiple results-set

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.

Customer Table                   
----------------------          
Id, Name,...                    
----------------------          

Orders Table                            
------------------------------          
OrderId, CustomerKey, Date,...          
------------------------------          

CustomerAddresses
-----------------------------------------------
AutoNumber, CustomerKey, Street, ZipCode,...
-----------------------------------------------

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:

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.

Example of how the results could come back:

Order-Customer Table
-------------------------------
Order.OrderId, Customer.Id, Customer.Name, Order.Date,....
-------------------------------

CustomerAddresses
-------------------------------
AutoNumber, CustomerKey, Street
-------------------------------

This is an example of a query that I currently generate:

SELECT [Order].[OrderId], [Order].[Date], [Customer].[Id], [Customer].[Name] 
FROM Order 
INNER JOIN [Customer] on [Order].[CustomerKey] = [Customer].[Id] 
WHERE ([Order].[Date] > '2015-06-28') 

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.

  1. Is there a better, simpler and more optimized way to achieve what I want?

Aucun commentaire:

Enregistrer un commentaire