I have to construct a query against our database that returns, in a single row per client, their basic contact info (from the [tblClients] table), get their latest order from [tblOrders] and a count of the items for that order from [tblOrderDetails]. As I understand it, I need to perform a subquery for the columns that will return the latest order, as well as a subquery to populate to totalQuantity field with a count of quantities per order.
I have no idea how to parameterise these subqueries, nor even if I am on the right track in my approach.
Here is a basic version of the three tables:
The result I am aiming towards is as follows:
2 | NRO | 5678 | blech | 34 | 2012-01-31 | 450 |
asked Apr 10 '12 at 08:19 AM in Default
To get the client details and quantity totals, you would be best to use a join
Then to get the latest order, you could use a subquery, or even a dervied table such as
Join this derived table onto the first query using the
This could even be done with a CTE - whichever is your own preference
answered Apr 10 '12 at 09:10 AM
Kev Riley ♦♦