select 
l.me_id,
l.CREATED_DATE,
st.location_name,
st.location_address_1,
st.location_city,
st.location_state_code,
st.location_postal_code,
l.mode_type,
li.weight,
li.hazmat,
li.PRODUCT_DESCRIPTION
from tp.table l
LEFT OUTER JOIN
(Select m.* from
(SELECT truck_ID,hazmat,sum(weight) as 'Weight', product_description,
Row_number() OVER(PARTITION BY ME_ID ORDER BY freight_ID DESC) AS R_NO
FROM [TP].[table2] where status='active'
GROUP BY ME_ID) m where R_NO=1) li
ON L.truck_ID=li.truck_id
LEFT OUTER JOIN
(Select n.* from
(SELECT ME_ID, location_name, location_address_1, location_city, location_state_code,location_postal_code, 
Row_number() OVER(PARTITION BY ME_ID ORDER BY stop_ID DESC) AS R_NO
FROM [TP].[table3] where status='active' and STOP_SEQUENCE_NUMBER = '1') n where R_NO=1) st
ON L.truck_ID=st.truck_id
where year(l.CREATED_date) ='2022' 
and LOCATION_address_1 LIKE '%6210 GLENWAY%'
or LOCATION_address_1  like '%480 WILEY%'
or LOCATION_address_1  like '%575 5TH%' -- not present in data  
or LOCATION_address_1 like '%100 E ROOSEVELT%'
or LOCATION_address_1 like '%565 5TH%' -- not present in data  
or LOCATION_address_1  like '%1001 SE TV%'
or LOCATION_address_1  like '%435 HUDSON%'
or LOCATION_address_1  like '%575 FIFTH%'-- not present in data  
or LOCATION_address_1  like '%3940 PLANK%'
or LOCATION_address_1  like '%609 SW 8TH%'
or LOCATION_address_1  like '%28145 HARRISON%' 
or LOCATION_address_1  like '%2620 SW 17Th%'
or LOCATION_address_1  like '%833 W 16TH%'
or LOCATION_address_1  like '%992 POQUONNOCK%'
or LOCATION_address_1  like '%4555 DANVERS%'
or LOCATION_address_1  like '%100 COMMERCE DR%'
or LOCATION_address_1  like '%6600 JEFFERSON%'

  SELECT ME_ID
        ,hazmat
        ,sum(weight) as 'Weight'
        ,product_description
        ,Row_number() OVER(PARTITION BY ME_ID ORDER BY freight_ID DESC) AS R_NO
    FROM [TP].[line_item]
   WHERE status='active'
GROUP BY ME_ID
        ,hazmat
        ,product_description
        ,freight_ID