Traditional approach:
SELECT c.Customer, o.*FROM dbo.Customers AS c
INNER JOIN dbo.Accounts AS a
ON c.ID = a.CustomerID
INNER JOIN dbo.Orders AS o
ON a.ID = o.AccountID
WHERE c.Inactive = 0
AND c.CustomerType = 1;
Alternative approach:
SELECT c.Customer, o.*FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
ON c.ID = o.CustomerID
WHERE c.Inactive = 0
AND c.CustomerType = 1;
We do something similar to the above. if you have alot of queries that make that join it does justify testing the improvement of all of the queries. in our case we found a large improvement in overall execution time of the queries we were able to simplify (executed several million times a day 2-3 ms makes a huge difference over a day). so if you can justify it with performance testing and showing an improvement i dont think there would be an issue.
The question then becomes why were AccountID and CustomerID seperate to begin with? Do you need both in the orders table?
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]