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, 2012 at 08:19 AM in Default

avatar image

valdonkie
40 1 1 3

Thanks, Kev. Your solution certainly seems the logical one. I will try it out and mark your answer ASAP.

Apr 10, 2012 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, 2012 at 09:10 AM

avatar image

Kev Riley ♦♦
64.2k 48 62 81

(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.

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:

x1016
x31
x28

asked: Apr 10, 2012 at 08:19 AM

Seen: 1063 times

Last Updated: Apr 10, 2012 at 09:18 AM

Copyright 2016 Redgate Software. Privacy Policy