Hi
I have a query that is returning the wrong amount of rows. Only by one in this instance. I cant figure our where the joins are incorrect. I have a feeling it is the relationship between dbo.vwWorksSchedule.PackRef = dbo.ProductPack.PackRef I have tried all sorts of joins within the query but cannot work it out. Any advice please. Thanks in advance.
I have a query that is returning the wrong amount of rows. Only by one in this instance. I cant figure our where the joins are incorrect. I have a feeling it is the relationship between dbo.vwWorksSchedule.PackRef = dbo.ProductPack.PackRef I have tried all sorts of joins within the query but cannot work it out. Any advice please. Thanks in advance.
SQL:
SELECT DISTINCT
dbo.WorksOrderHeader.udfType, dbo.Product.Width, dbo.Product.Thickness, dbo.Product.Length, dbo.WorksOrderHeader.udfRunSpeed, dbo.WorksOrderHeader.udfSplitFactor,
dbo.WorksOrderHeader.udfPiecesinSheet, dbo.WorksOrderHeader.udfProfileCode, dbo.Product.ProductCode, dbo.ProductPack.NoOfPieces, dbo.WorksOrderHeader.WorksOrderNumber,
dbo.vwWorksSchedule.Name, dbo.[148-vwWOFinishedProducts].ProductCode AS [Finished Product], dbo.[148-vwWOFinishedProducts].Thickness AS FProdThickness,
dbo.[148-vwWOFinishedProducts].Width AS FProdWidth, dbo.[148-vwWOFinishedProducts].Length AS FProdLength, dbo.[148-vwWOFinishedProducts].DefaultPiecesPerPack,
dbo.WorksOrderSchedule.ScheduleNumber
FROM dbo.[148-vwWOFinishedProducts] INNER JOIN
dbo.vwWorksSchedule INNER JOIN
dbo.WorksOrderSchedule ON dbo.vwWorksSchedule.WorksOrderScheduleID = dbo.WorksOrderSchedule.WorksOrderScheduleID INNER JOIN
dbo.WorksOrderHeader INNER JOIN
dbo.WorksOrderScheduleLine ON dbo.WorksOrderHeader.WorksOrderID = dbo.WorksOrderScheduleLine.WorksOrderID ON
dbo.vwWorksSchedule.WorksOrderScheduleID = dbo.WorksOrderScheduleLine.WorksOrderScheduleID AND dbo.vwWorksSchedule.WOSLineNumber = dbo.WorksOrderScheduleLine.LineNumber AND
dbo.vwWorksSchedule.WOSLineType = dbo.WorksOrderScheduleLine.LineType ON dbo.[148-vwWOFinishedProducts].WorksOrderID = dbo.WorksOrderHeader.WorksOrderID RIGHT OUTER JOIN
dbo.Product INNER JOIN
dbo.ProductPack ON dbo.Product.ProductID = dbo.ProductPack.ProductID ON dbo.vwWorksSchedule.PackRef = dbo.ProductPack.PackRef FULL OUTER JOIN
dbo.AVO ON dbo.Product.ProductID = dbo.AVO.ProductID AND dbo.vwWorksSchedule.AVOName = dbo.AVO.Name
WHERE (dbo.WorksOrderScheduleLine.LineType = 1) AND (dbo.WorksOrderSchedule.ScheduleNumber = 23301)