SQLTeam.com | Weblogs | Forums

SQL Query - One to many


#1

I have two tables (ShipmentHeaders and ShipmentAPAR) where my shipment info is stored in ShipmentHeaders and is controlled by PK_OrderNo. The costs of the shipments are stored in ShipmentAPAR.

What I am after is to run a query based off a customers name and for query to find all the related Order Numbers in the ShipmentHeaders and then pull up the total amount (Sum) of any entries found in ShipmentAPAR table for FK_OrderNo.

Heres a layout of my tables:

ShipmentHeader:
PK_OrderNo
CustomerNo
PUDate
DeliveryDate

ShipmentAPAR:
PK_Number
FK_OrderNo
Description
Total

Data:
ShipmentHeader:
1 2100 7/15/15 7/16/15
2 2101 7/16/15 7/16/15
3 2100 7/16/15 7/17/15

ShipmentAPAR:
1 1 Miles 210.52
2 1 Crating 140.21
3 1 Fuel 245.00
4 2 Fuel 245.00
5 3 Fuel 245.00

Thanks for your help in this!


#2

To query by a customer name, you will also need info from the table where customer names are stored, presumably along with the customerNo. If you already know the CustomerNo of the customer that you want to query for,

SELECT
	SUM(Total) AS [TotalCostOfShipment]
FROM
	ShipmentHeader sh
	INNER JOIN ShipmentAPAR sa ON
		sa.FK_OrderNo = sh.PK_OrderNo
WHERE
	sh.CustomerNo = 12345 -- or the customer number of the customer you are interested in.

#3

Thank you for your help so far James. This is the query that I have constructed so far

Use AirTrak
SELECT PK_OrderNo
      ,Housebill
      ,OrgAirport
      ,DestAirport
      ,CallIn
      ,CallInName
	  ,Description
      ,APARType
	  ,Total
	  ,TotalMiles
	  ,OrderDate
	  ,PODDateTime
      ,ShipmentDateTime
      ,InvoiceDateTime

From dbo.tbl_ShipmentHeader as SH
	 Inner Join dbo.tbl_ShipmentAPAR as SC
	  On SH.PK_OrderNo = SC.FK_OrderNo
		where SH.BillNo = 2003 and
		SC.APARCode= 'C'

With the query you gave me, would I need to add it in as a sub query to mine to return the results I am after?

For instance, Order Number 4915 has 4 customer charges, so after it displays the 4 entries as predicted, but I want to have a new row that shows the total charges after the listed entries. Here is a snipit of what I get returned currently.


#4

Try using GROUPING SETS, like shown below. You can add additional sets into the grouping sets if you need subtotals.

Use AirTrak
SELECT PK_OrderNo
      ,Housebill
      ,OrgAirport
      ,DestAirport
      ,CallIn
      ,CallInName
	  ,Description
      ,APARType
	  ,SUM(Total) AS Total
	  ,TotalMiles
	  ,OrderDate
	  ,PODDateTime
      ,ShipmentDateTime
      ,InvoiceDateTime

From dbo.tbl_ShipmentHeader as SH
	 Inner Join dbo.tbl_ShipmentAPAR as SC
	  On SH.PK_OrderNo = SC.FK_OrderNo
		where SH.BillNo = 2003 and
		SC.APARCode= 'C'
GROUP BY GROUPING SETS
(
	(PK_OrderNo
      ,Housebill
      ,OrgAirport
      ,DestAirport
      ,CallIn
      ,CallInName
	  ,Description
      ,APARType
	  ,TotalMiles
	  ,OrderDate
	  ,PODDateTime
      ,ShipmentDateTime
      ,InvoiceDateTime
     ),
     ()
)