T-SQL: Joining Tables

Hi:

Below are three blocks of T-SQL code, each followed by an image of the records retrieved from the specific block above it.

I'm trying to develop a manufacturing "Work in Process" report showing data from the PNMAST table  where the data in the BM010115.CPN_I field is in the BM010115.PPN_I field and BM010115.SUBCAT_I = 1.

Please help me modify the syntax of the first T-SQL code block - after its UNION ALL clause - to accommodate this.

The main thing is that I don't want 506 as the FormulaId of the PNMAST table.

Finally, please keep in mind that the images that I have attached are simply the first few records of the tables and not all of the hundreds or even thousands of records in those tables/.

Thank you!

Software Engineer

select NULL as [LOTSEQNBR], FG.BatchNo as [FGBATCH], FG.FormulaId as [FG], FG.[Labor Cost] as [FGLABOR], FG.[OverHead Cost] as [FGOH], FG.[Material Cost] as [FGMAT], 
FG.[Labor Cost] + FG.[OverHead Cost] + FG.[Material Cost] as [FGCOST],
NULL as [FORMULA],
NULL as [FORMULAMAT], NULL AS [FORUMLALABOR], NULL AS [FORMULAOH],
NULL AS [Component], NULL as [LOT], NULL as [LOTQTY], NULL as [LOTUNITCOST]
from BM_View_Dashboard_ProductionCostAnalysis FG
INNER JOIN BM010115 BOM ON FG.FormulaId = BOM.PPN_I
where FG.FormulaId = '506' and FG.BatchNo = '50418G12D' and BOM.SUBCAT_I = 1
UNION ALL
select DISTINCT NULL as [LOTSEQNBR], NULL as [FGBATCH], NULL as [FG], NULL as [FGLABOR], NULL as [FGOH], NULL as [FGMAT], NULL as [FGCOST], BOM.PPN_I as [FORMULA],
PROD.BatchMatCostActual as [FORMULAMAT], PROD.BatchLabortCostActual AS [FORMULALABOR], PROD.BatchOverHeadCostActual AS [FORMULAOH],
--CASE WHEN PROD.BatchMatCostActual = 0 and PROD.BatchLabortCostActual = 0 and 
--PROD.BatchOverHeadCostActual = 0
--THEN 
INV.ITEMNMBR as [Component], 
NULL as [LOT], NULL as [LOTQTY], NULL as [LOTUNITCOST]
from PNMAST PROD
INNER JOIN BM010115 BOM ON PROD.FormulaId = BOM.PPN_I
INNER JOIN BMMTrxHdr BMHDR ON PROD.BatchNo = BMHDR.BMDocNo
INNER JOIN BMMTrxDtl BMDTL ON BMHDR.Dex_Row_Id = BMDTL.RefDex_Row_Id
INNER JOIN IV30300 INV ON BMDTL.LNSeqNo = INV.LNSEQNBR 
where --PROD.FormulaId IN ('506N')
BMHDR.IVDocNmbr IN ('081007', '081008', '081009')
and INV.LNSEQNBR = 16384 and INV.ITEMNMBR IN ('506N', '1880', '1355') 
and INV.DOCTYPE = 1 --and INV.DOCNUMBR IN ('081007', '081008', '081009')

Select all Open in new window

select * from BM010115 where PPN_I = '506'

Select all Open in new window

select * from PNMAST where FormulaId = '506N' 

Select all Open in new window