troubleshooting Question

Another way of writing this SQL

Avatar of Camillia
CamilliaFlag for United States of America asked on
Microsoft SQL Server 2005
8 Comments1 Solution146 ViewsLast Modified:
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'
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros