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:cliID cliName phone Email orderID orderDate totalQuantity 1 NYTimes 1234 blah 12 2011-12-12 352 2 NRO 5678 blech 34 2012-01-31 450
asked Apr 10, 2012 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, 2012 at 09:10 AM
Kev Riley ♦♦