Hi @v-rzhou-msft ,
Thanks for replying!
The query I shared with you guys is part of a big query. I am sharing only the part of the query that joining tables start, there are more fields include in this query I thought which won't make sense to share except taking spaces. The query is in BOLD line
T2.PrdStdCst AS 'Component Prod Std Cost',
(T0.Quantity * T2.PrdStdCst) AS 'Extended Prod Std Cost',
(SELECT T5.price FROM ITM1 T5 WHERE T0.Code = T5.ItemCode AND T5.PriceList = 3) AS 'Cost Estimate',
(SELECT T5.price * T0.quantity FROM ITM1 T5 WHERE T0.Code = T5.ItemCode AND T5.PriceList = 3) AS 'Extended Cost Estimate',
T2.LastPurPrc AS 'Last Purchase Price',
(T2.LastPurPrc * T0.Quantity) AS 'Extended Last Purchase Price'
FROM [dbo].[ITT1] T0
INNER JOIN OITM T1 ON T0.Father = T1.ItemCode
LEFT OUTER JOIN OITM T2 ON T0.Code = T2.ItemCode
LEFT OUTER JOIN ORSC T3 ON T0.Code = T3.VisResCode
LEFT OUTER JOIN
SELECT T0.ItemCode, '05-MRB' AS Whse
FROM OITW T0
INNER JOIN (SELECT DISTINCT ItemCode FROM OITW WHERE WhsCode = '05-MRB') T1 ON T0.ItemCode=T1.ItemCode
GROUP BY T0.ItemCode
) T4 ON T0.Father=T4.ItemCode
WHERE T4.Whse IS NULL
ORDER BY T0.Father, T0.visorder
Below is a screenshot from the data, so basically all the fields pulled from other tables in the query and the part of the join which is in parentheses (Italic Font) only filters to show specific data.
I hope this is what you need to find the solution.
Thank you so much,