Help with query

[Cpreston]

Hi

I have a query (well view) that is running ok until I add the table ProductStockoption. Before adding it I 43 records, when it is added I then have 430 rows, which makes totals incorrect of course. I have tried different joins etc... but cannot get it to give the correct result. Any ideas what I can do to change the query please. (as soon as I take out the productstockoption table all works again) Thanks in advance

SELECT TOP (100) PERCENT dbo.ProductAnalysis.CalendarYear, DATENAME(month, DATEADD(month, dbo.ProductAnalysis.CalendarMonth - 1, 0)) AS Monthname,
dbo.ProductAnalysis.QuantityUsed, dbo.ProductAnalysis.BranchID, dbo.ProductAnalysis.ProductID, dbo.Product.ProductCode, dbo.Product.Description,
dbo.ProductGroup.Name, dbo.ProductGroup.ParentID, dbo.ProductGroup.Deleted
FROM dbo.ProductAnalysis INNER JOIN
dbo.Product ON dbo.ProductAnalysis.ProductID = dbo.Product.ProductID INNER JOIN
dbo.ProductGroup ON dbo.Product.ProductGroupID = dbo.ProductGroup.ProductGroupID INNER JOIN
dbo.ProductStockOption ON dbo.Product.ProductID = dbo.ProductStockOption.ProductID
WHERE (dbo.ProductAnalysis.CalendarYear = 2015) AND (dbo.ProductAnalysis.BranchID = 1) AND (dbo.ProductAnalysis.ProductID = 5016) AND
(NOT (dbo.Product.ProductCode LIKE 'zz%')) AND (dbo.ProductGroup.ParentID = 5000) AND (dbo.ProductGroup.Deleted = 0)
ORDER BY Monthname

 

[bborissov]

How many rows for one ProductId you have in ProductStockOption?
In the query you will have as many records for one ProductId as you have them in ProductStockOption.

BTW I don't see why you need ProductStockOption in that query?
You didn't have any field selected from that table.

Borislav Borissov
VFP9 SP2, SQL Server

 

[Cpreston]

Hi

I needed fields from ProductStockOption but no added them in yet.
I solved the problem, I needed to add the BranchID in from ProductStockOption = 1 also. All working now

Thanks