select
o.orderid,
max(o.orderstatus) as status,
sum(od.quantity*od.vendor_price) + max (tn.shipment_cost) as cost,
max (tn.shipment_cost) as shipping
from orders o
join orderdetails od on o.orderid = od.orderid
join (
	select orderid, sum(shipment_cost) as shipment_cost
	from trackingnumbers 
	where form like '%Hrz-%'
	group by orderid
) tn on od.orderid = tn.orderid

where (od.productnote LIKE '%HRZ % % % DS%' OR
od.productnote LIKE '%HRZ % % MDS%')
AND o.orderid in (
4393788,
4397199,
4381415,
4382138,
4288257,
4397510,
4397380,
4347329,
4347552,
4347712,
4349458,
4359384,
4364491,
4377689,
4386305,
4387246,
4395698,
4396624,
4396897,
4397387,
4397582,
4387836,
4396842,
4397267,
4397489,
4365040,
4396972,
4397019,
4397156,
4397157,
4397589,
4342459,
4394339,
4397662,
4397930,
4397438,
4397513,
4397714,
4397723,
4397799,
4397807,
4397827,
4397864,
4397904,
4397998,
4398014,
4397080,
4397081,
4397094,
4397502,
4397517,
4397667,
4397897,
4397192,
4388740,
4397078,
4397342,
4397433,
4397485,
4397509,
4397511,
4397620,
4397634,
4397129,
4398068,
4397701,
4397758,
4397790,
4398115,
4398192,
4336190,
4384725,
4396127,
4396120,
4398056,
4398404,
4398759,
4380484,
4380492,
4364553,
4392310,
4398290,
4384914,
4398530,
4398560,
4398684,
4398754,
4398821,
4256799,
4267645,
4379472,
4397902,
4397878,
4398161,
4398242,
4398402,
4398403,
4398686,
4398528,
4398033,
4398467,
4398583,
4398982,
4398990,
4398997,
4399163,
4399201,
4399301,
4399391,
4397689,
4398263,
4398302,
4398892,
4398978,
4398988,
4398992,
4399029,
4399270,
4399392,
4326756,
4397890,
4398314,
4398512,
4387058,
4395147,
4378841,
4398293,
4399039,
4399107,
4399481,
4399412,
4399414,
4399416,
4399570,
4399408,
4399572,
4399607,
4399716,
4399666,
4399945,
4399904,
4400233,
4391266,
4399681,
4399659,
4399776,
4399948,
4400006,
4370620,
4388214,
4399658,
4399795,
4399946,
4399884,
4399984,
4400075,
4400076,
4400195,
4400241,
4400286,
4400326,
4400327,
4400368,
4400455,
4399663,
4373875,
4400231,
4400674,
4400675,
4400582,
4368580,
4400712,
4400881,
4400910,
4400989,
4400751,
4401076,
4398559,
4400822,
4400947,
4400695,
4401229,
4390780,
4391741,
4400946,
4390431,
4400678,
4400923,
4401009,
4401081,
4401114,
4401208,
4401268,
4401470,
4401335,
4401361,
4401494,
4401549,
4401594,
4400696,
4400776,
4400871,
4400920,
4400976,
4400990,
4401060,
4401285,
4401604,
4401660,
4401765,
4397960,
4401850,
4401852,
4330864,
4362818,
4368468,
4401936,
4392540,
4401676,
4402243,
4402561,
4402792,
4402794,
4402734,
4403032,
4403062,
4403251,
4403344,
4403888,
4404336,
4404577,
4373591,
4381461,
4398394,
4401981,
4402613,
4402711,
4402885,
4403582,
4404134,
4404195,
4404208,
4404250,
4404289,
4404353,
4401742,
4380211,
4393684,
4398472,
4398683,
4401898,
4402342,
4402896,
4402948,
4403002,
4403023,
4403223,
4403258,
4403883,
4404194,
4401792,
4397213,
4401920,
4402035,
4402048,
4402201,
4401988,
4402235,
4402325,
4402543,
4402554,
4402814,
4402847,
4402985,
4403144,
4403216,
4397410,
4402051,
4402101,
4402280,
4402532,
4402705,
4402777,
4402892,
4402826,
4402887,
4403318,
4403541,
4403640,
4403651,
4403790,
4403891,
4403845,
4403848,
4403854,
4403636,
4404064,
4404239,
4404456,
4404505,
4404518,
4403936,
4404261,
4402559,
4404512,
4397685,
4403299,
4403416,
4404320,
4404649,
4404667,
4404643,
4405130,
4405233,
4404580,
4404635,
4404699,
4404783,
4401794,
4404889,
4405157,
4405217,
4405221,
4405182,
4405197


)

group by o.orderid