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.
- Is there a better, simpler and more optimized way to achieve what I want?
Aucun commentaire:
Enregistrer un commentaire