troubleshooting Question

Complex SQL Statement

Avatar of paiman01
paiman01 asked on
DatabasesMicrosoft SQL Server 2005SQL
5 Comments1 Solution336 ViewsLast Modified:
I have the below SQL statement which works.  My challenge is that I need to do the same count of the category codes but multiple times for different values.  So as an example (((Count(tbl_Asset_Inspection.CATEGORY_CODE))=2)), (((Count(tbl_Asset_Inspection.CATEGORY_CODE))=3)), etc.

Thank you for your help.
SELECT tbl_Unit.Name, tbl_Unit.Location, tbl_Asset_Inspection.NSN, tbl_Asset.NOMENCLATURE, tbl_LocationAssets.CAT_III, tbl_LocationAssets.CAT_IV, tbl_LocationAssets.CAT_II_IV, Count(tbl_Asset_Inspection.CATEGORY_CODE) AS CountOfCATEGORY_CODE
FROM tbl_Asset INNER JOIN ((tbl_LocationAssets INNER JOIN tbl_Asset_Inspection ON tbl_LocationAssets.iAssetID = tbl_Asset_Inspection.iParentID) INNER JOIN tbl_Unit ON tbl_LocationAssets.iParentID = tbl_Unit.iParentID) ON tbl_Asset.ID = tbl_Asset_Inspection.iParentID
GROUP BY tbl_Unit.Name, tbl_Unit.Location, tbl_Asset_Inspection.NSN, tbl_Asset.NOMENCLATURE, tbl_LocationAssets.CAT_III, tbl_LocationAssets.CAT_IV, tbl_LocationAssets.CAT_II_IV
HAVING (((Count(tbl_Asset_Inspection.CATEGORY_CODE))=1))
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
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 5 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