asked on

Another way of writing this SQL

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?

SELECT 
        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'

Open in new window

Guy Hengel [angelIII / a3]

if you want to count, you shall even use INNER JOIN and no LEFT JOIN?
anyhow, if you change the whole "JOINs" into 1 EXISTS() clause, you could remove the DISTINCT clause:

SELECT  COUNT(*) AS [correctItemCount]#
  FROM [Item] I   
WHERE EXISTS( SELECT NULL
               FROM  [Family] F  
               INNER JOIN [Classification] CL  ON CL.[Id] = F.[ClassificationId]  
               INNER JOIN [FamilyText] FT  ON FT.[Id] = F.[Id] AND FT.[LocaleId] = '1003'  
              WHERE F.[Id] = I.[FamilyId] 
             )
 AND EXISTS ( SELECT NULL
                FROM [xCategoryItem] xCI  
                INNER JOIN [Category] PC  on  xCI.[L] BETWEEN PC.[L] AND PC.[R]
                INNER JOIN [Category] C  ON C.[Id] = xCI.[CategoryId] 
                INNER JOIN [CategoryText] CT ON CT.[Id] = C.[Id] AND CT.[LocaleId] = '1003'
               WHERE xCI.[ItemId] = I.[Id]
             )
 AND EXISTS ( SELECT NULL
                FROM [xProduct] xP  
               WHERE xP.[ItemCode] = I.[Code] 
                 AND xP.CanOrderIfActive = 1 
             )

Open in new window

Camillia

ASKER

let me try it. will post back. I didnt write the orig sql. Developers who wrote it dont work here anymore...

Bad performance with the Distinct?

Camillia

ASKER

No, i dont think i can use that. I'm sorry but i should've added the "where" clause and the "select" part...i get errors because of them:

see below

Select part:
SELECT (C.Depth - PC.Depth) AS [Depth],-- C.Depth ,PC.Depth,
        C.Id AS [Id], 
        C.ParentId AS [ParentId], 
        COUNT(DISTINCT I.Id) AS [correctItemCount], 
        COUNT(distinct I.Id) AS [ItemCount], 
        ISNULL(CT.[Name],C.[Name]) AS [Name]  
 
--- where part:
I.IsActive = 1 
        AND CL.Code = 'ABC' 
        AND PC.Id = 'EC'
        AND ((C.Depth - PC.Depth) >= 1
        AND (C.Depth - PC.Depth) <= 4)
 
 GROUP BY (C.Depth - PC.Depth), --C.Depth , PC.Depth,
 C.Id, 
 C.L, 
 C.ParentId, 
 ISNULL(CT.[Name], C.[Name]) 
  ORDER BY C.L ASC

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

and your query would become:

SELECT (C.Depth - PC.Depth) AS [Depth],-- C.Depth ,PC.Depth,
        C.Id AS [Id], 
        C.ParentId AS [ParentId], 
        dbo.GetCorrectItemCount(I.Id) AS [correctItemCount],

Open in new window

Camillia

ASKER

ah, let me try it.

SOLUTION

J_Carter

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Camillia

ASKER

i will try that as well