Microvellum Knowledge Network

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!

    MVU eLearning



    Grow Your Knowledge
    Follow along with RJ as he takes you on a journey to build your foundational knowledge of Toolbox.


      Follow us on:

               

        Google Review