In this new system i'm working on, there's a SQL with cross-join. I removed the cross join but I still have to keep the "distinct". I thought if i remove the cross join, i can remove the distinct. I have to keep it there otherwise, results will be more...
Also, see how "category" is joined twice..anyway to change that?
COUNT(DISTINCT I.Id) AS [correctItemCount],
FROM [Item] I
INNER JOIN [Family] F ON F.[Id] = I.[FamilyId]
INNER JOIN [Classification] CL ON CL.[Id] = F.[ClassificationId]
LEFT OUTER JOIN [FamilyText] FT ON FT.[Id] = F.[Id] AND FT.[LocaleId] = '1003'
--CROSS JOIN [Category] PC ***changed it to leftjoin below
INNER JOIN [xCategoryItem] xCI ON xCI.[ItemId] = I.[Id]
left join [Category] PC on xCI.[L] BETWEEN PC.[L] AND PC.[R]
INNER JOIN [xProduct] xP ON xP.[ItemCode] = I.[Code] AND xP.CanOrderIfActive = 1
INNER JOIN [Category] C ON C.[Id] = xCI.[CategoryId]
LEFT OUTER JOIN [CategoryText] CT ON CT.[Id] = C.[Id] AND CT.[LocaleId] = '1003'