Avatar of Camillia
CamilliaFlag for United States of America 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

Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Camillia

8/22/2022 - Mon
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

ASKER
Camillia

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?
ASKER
Camillia

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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

ASKER
Camillia

ah, let me try it.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Camillia

i will try that as well
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.