Need help with this query so it returns only a single record per user
-
We have a group of users who can access various locations within the datacenter. For the largest group of users, if they can access one location they can access all of the locations. For a very small population, the user can only access the office and not the data center locations. These users are causing me a problem. The query is aimed at basically giving me the count of active locations. But in the mixed case I get BOTH a record with a null count AND a record with access count. In otherwords: Amy can access only the office and cannot access dc1 or dc2. The query returns Amy's record as Amy, null and Amy, 1. I want it to return ONLY Amy, 1. Any suggestions on the changes I need to accomplish that? Here is the current query:
SELECT dbo.ContactMaster.BmcCustKey,
(SELECT COUNT(dbo.LocationAccessGrant.CanAccessLocation)
WHERE (dbo.LocationAccessGrant.CanAccessLocation = 1)) AS AccessCount
FROM dbo.ContactMaster LEFT OUTER JOIN
dbo.LocationAccessGrant ON dbo.ContactMaster.CompanyID = dbo.LocationAccessGrant.CompanyID AND
dbo.ContactMaster.ContactIdentity = dbo.LocationAccessGrant.ContactIdentity
GROUP BY dbo.ContactMaster.BmcCustKey, dbo.LocationAccessGrant.CanAccessLocation
ORDER BY dbo.ContactMaster.BmcCustKey -
We have a group of users who can access various locations within the datacenter. For the largest group of users, if they can access one location they can access all of the locations. For a very small population, the user can only access the office and not the data center locations. These users are causing me a problem. The query is aimed at basically giving me the count of active locations. But in the mixed case I get BOTH a record with a null count AND a record with access count. In otherwords: Amy can access only the office and cannot access dc1 or dc2. The query returns Amy's record as Amy, null and Amy, 1. I want it to return ONLY Amy, 1. Any suggestions on the changes I need to accomplish that? Here is the current query:
SELECT dbo.ContactMaster.BmcCustKey,
(SELECT COUNT(dbo.LocationAccessGrant.CanAccessLocation)
WHERE (dbo.LocationAccessGrant.CanAccessLocation = 1)) AS AccessCount
FROM dbo.ContactMaster LEFT OUTER JOIN
dbo.LocationAccessGrant ON dbo.ContactMaster.CompanyID = dbo.LocationAccessGrant.CompanyID AND
dbo.ContactMaster.ContactIdentity = dbo.LocationAccessGrant.ContactIdentity
GROUP BY dbo.ContactMaster.BmcCustKey, dbo.LocationAccessGrant.CanAccessLocation
ORDER BY dbo.ContactMaster.BmcCustKeyNot sure I've understood you properly, but your subquery doesn't look correct. So try something like this:
SELECT dbo.ContactMaster.BmcCustKey
,Sum(CASE WHEN dbo.LocationAccessGrant.CanAccessLocation = 1
THEN 1
ELSE 0
END) accesscount
FROM dbo.ContactMaster
LEFT OUTER JOIN dbo.LocationAccessGrant
ON dbo.ContactMaster.CompanyID = dbo.LocationAccessGrant.CompanyID
AND dbo.ContactMaster.ContactIdentity = dbo.LocationAccessGrant.ContactIdentity
GROUP BY dbo.ContactMaster.BmcCustKey
,dbo.LocationAccessGrant.CanAccessLocation
ORDER BY dbo.ContactMaster.BmcCustKeyIt should take care of your null values
"When did ignorance become a point of view" - Dilbert