SQL Server – Remove Duplicate Results via Subqueries in Join Conditions

duplicationjoin;sql-server-2000subquery

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 

Best Answer

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,