Removing duplicate results via subqueries in reference to join conditions for multiple tables

Question

With the code below I was originally attempting to not use sub queries to eliminate "duplicate data" (its technically not duplicate data since each order has several lines of data where prices and weights are different but I wanted to consolidate my results to where I only get distinct order #'s) But with having no luck of only using join conditions and SUM/GROUP BY. I adopted in my first join a sub query to have distinct orders returned with the fields "price" and "shipped". And that worked accordingly and I received the orders I needed. But with utilizing the whole script I realized "carton_no" was causing "duplicate" results being returned I so attempted to add another sub query but I seem to be receiving the error:

Msg 107, Level 16, State 2, Line 17 The column prefix 'TCT' does not match with a table name or alias name used in the query.

Block of code providing errors:

---

LEFT OUTER JOIN 
 (
 SELECT TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no FROM tdc_revshelf_carton_box TRCB WITH (NOLOCK) 
 INNER JOIN tdc_carton_tx TCT 
 ON TRCB.order_no = TCT.order_no
 AND TRCB.order_ext = TCT.order_ext
 AND TRCB.carton_no = TCT.carton_no 
 GROUP BY TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no
 ) TRCB ON TRCB.order_no = TCT.tdc_carton_tx 

---

DECLARE @location VARCHAR(10)
DECLARE @dateshipped DATETIME 
SET @location = 'RAS 2'
SET @DateShipped = DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())-3)
SELECT 
 SUM (L.Shipped ) AS Shipped
 , SUM (L.Price ) AS Price
 , O.Order_no
 , O.Ext
 , O.Cust_Code 
 , O.Ship_To_Name 
 , O.Freight_Allow_Type 
 , TCT.Carton_no 
 , TPM.dim_ext_x 
 , TPM.dim_ext_y 
 , TRCB.Number_of_Boxes 
 , MAX(TRCB.Skid_Height_Inches )
 , MAX(O.Routing) AS 'Routing' 
 , MAX(O.User_def_fld9) AS 'bol_no' 
 , MAX(Ship_to_name) AS Ship_to_name_1 
 , MAX(Ship_to_add_3) AS Ship_to_add_3 
 , MAX(L.[location]) AS 'Location' 
 , MAX(O.cust_code ) AS 'Cust_code_1' 
 , MAX(O.user_def_fld10) AS 'Skids' 
 , MAX(O.user_def_fld12) AS 'Weight' 
 , ISNULL(MAX(BH.est_freight_cost),0) AS 'BOL_est_Freight'
 , TCT.[weight] + TPM.[weight] AS 'Total_Pallet_Weight'
FROM orders O WITH (NOLOCK)
INNER JOIN 
 (
 SELECT o.order_no, L.shipped, L.price FROM orders O WITH (NOLOCK) INNER JOIN Ord_list L ON O.order_no = L.order_no
 WHERE O.routing NOT LIKE 'FEDX%' 
 AND O.routing NOT IN ('UPS', 'UPS 1', 'UPS 2', 'UPS 3') 
 AND ISNULL(O.void,'') <> 'V'
 AND L.location = @location
 GROUP BY O.order_no, L.shipped, L.price
 )
 L ON O.order_no = L.order_no
JOIN xfers X
 ON O.order_no = X.xfer_no
JOIN xfer_list XL
 ON X.xfer_no = XL.xfer_no
--INNER JOIN ord_list L WITH (NOLOCK)
-- ON O.order_no = L.order_no
-- AND O.ext = L.order_ext
INNER JOIN arcust C WITH (NOLOCK)
 ON O.cust_code = C.customer_code 
LEFT OUTER JOIN armaster A WITH (NOLOCK)
 ON A.customer_code = O.cust_code 
 AND A.ship_to_code = O.ship_to
 AND A.location_code = O.location 
LEFT OUTER JOIN tdc_pkg_master TPM WITH (NOLOCK)
 ON TCT.carton_type=TPM.pkg_code
INNER JOIN tdc_carton_tx TCT WITH (NOLOCK)
 ON TPM.pkg_code = TCT.carton_type
LEFT OUTER JOIN 
 (
 SELECT TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no FROM tdc_revshelf_carton_box TRCB WITH (NOLOCK) 
 INNER JOIN tdc_carton_tx TCT 
 ON TRCB.order_no = TCT.order_no
 AND TRCB.order_ext = TCT.order_ext
 AND TRCB.carton_no = TCT.carton_no 
 GROUP BY TRCB.order_no, TRCB.Skid_Height_Inches, TCT.carton_no
 ) TRCB
 ON TRCB.order_no = TCT.tdc_carton_tx 
LEFT OUTER JOIN ras_bol_details BD WITH (NOLOCK)
 ON X.xfer_no = BD.bl_src_no
 AND X.xfer_no = BD.bl_src_no AND BD.order_type IN ('T','S')
LEFT OUTER JOIN ras_bol_header BH WITH (NOLOCK)
 ON BD.bl_no = BH.bl_no
 AND O.order_no = BD.bl_src_no 
 AND O.ext = BD.bl_src_ext 
WHERE DATEADD(day, 0, DATEDIFF(day, 0, O.date_shipped)) >= @dateshipped
GROUP BY
 O.order_no
 , O.ext
 , O.cust_code
 , O.ship_to_name 
 , O.freight_allow_type 
 , TCT.carton_no 
 , TCT.[weight]
 , TPM.[weight]
 , TPM.dim_ext_x 
 , TPM.dim_ext_y 
 , TRCB.number_of_boxes 
 , TRCB.skid_height_inches 

• Aaron Bertrand: DISTINCT and GROUP BY? Why? Also, please don't use shorthand like dd. Your life won't change by spelling it out (day is just one extra character), and you can avoid a lot of problems (see here and here).
• tfenwick11: Right I never got around to changing the dd, and I know the GROUP BY is supposed to replace DISTINCT. But with including DISTINCT I was receiving the exact orders I needed vs just having the GROUP BY only.
• tfenwick11: @AaronBertrand my mistake, I receive the correct amount of orders with or without distinct. My apology must be going crazy this morning..
• RDFozz: I suspect (but am not sure) that in some way the problem lies in your using the TCT alias in both the main query and in a sub-query. I'd trying changing one or the other, just to make sure that the multiple references aren't confusing either the engine, or you!

Answer 1

This has been resolved, with restructuring the procedure and learning a few columns could be removed that were not needed in the report. I was able to move away from the second sub query. Thanks,