SQL Joins for Work Order Parts

SQL Joins for Work Order Parts

Hello,

Does anyone happen to know what the table joins are to recreate the work order window, but to view all parts among active work orders? We are set up with a unified WorkOrder DB so not sure if my joins are correct or not. I got close using the data dictionary but getting duplicate part names. What I have so far:

Select 
PROJ.Name as ProjectName,
PROD.RoomName as RoomName,
WORKORDERS.Name as WorkOrderNameMV,
PROD.Name as ProductName,
PROD.ItemNumber as ProductNumber,
PROD.Quantity as ProductQuantity,
PARTS.Name as PartName,
ROW_NUMBER() OVER (ORDER BY PARTS.Name) PartRowNumber,
PARTS.CutPartWidth,
PARTS.CutPartLength,
PROD.Width as ProductWidth,
PROD.Depth as ProductDepth,
PROD.Height as ProductHeight
From [Microvellum_Workorder].[dbo].Parts PARTS
left join [Microvellum_Workorder].[dbo].PartsProcessingStations PARTSPS on PARTSPS.LinkIDPart = PARTS.LinkID
left join [MV DATA 47].[dbo].WorkOrders WORKORDERS on WORKORDERS.LinkID = PARTSPS.LinkIDWorkOrder
left join [Microvellum_Workorder].[dbo].WorkOrderBatches BATCH on BATCH.LinkID = PARTSPS.LinkIDBatch
left join [Microvellum_Workorder].[dbo].Products PROD on PROD.LinkID = PARTS.LinkIDProduct
left join [MV DATA 47].[dbo].Projects PROJ on PROJ.LinkID = PROD.LinkIDProject
Where WORKORDERS.Name is not null 
Order by ProjectName ASC, RoomName ASC, ProductNumber ASC, PartName ASC

Thanks!

    Follow us on: