x

parameterised subquery as field in result set

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:

[tblClients] --Client info table
cliID int,
cliName varchar,
phone varchar,
Email varchar

[tblOrders] --order information. many records per client
orderID int,
cliID int,
orderDate datetime

[tblOrderDetails] --order details. many records per order
odID int,
orderID int,
productID int,
quantity int

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 |
more ▼

asked Apr 10 '12 at 08:19 AM in Default

valdonkie gravatar image

valdonkie
40 1 1 1

Thanks, Kev. Your solution certainly seems the logical one. I will try it out and mark your answer ASAP.
Apr 10 '12 at 09:18 AM valdonkie
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

To get the client details and quantity totals, you would be best to use a join

select
 tblClients.cliID,
 tblClients.cliName,
 tblClients.phone,
 tblClients.email,
 sum(quantity) as TotalQuantity
from
 tblClients
 join tblOrders on tblClients.cliID = tblOrders.cliID
 join tblOrderDetails on tblOrders.OrderID = tblOrderDetails.OrderID
group by
 tblClients.cliID,
 tblClients.cliName,
 tblClients.phone,
 tblClients.email

Then to get the latest order, you could use a subquery, or even a dervied table such as

select
 cliID,
 orderID,
 OrderDate
from (
    select
     cliID,
     orderID,
     OrderDate,
     row_number()over(partition by cliID order by OrderDate desc) as RowNumber
    from tblOrders
     )latestOrders
where RowNumber = 1

Join this derived table onto the first query using the cliID.

This could even be done with a CTE - whichever is your own preference

more ▼

answered Apr 10 '12 at 09:10 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 43 49 76

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x673
x22
x18

asked: Apr 10 '12 at 08:19 AM

Seen: 749 times

Last Updated: Apr 10 '12 at 09:18 AM