title: SQL: Linking a Count to a specific value through multiple tables
body: <p>I'm trying to link a COUNT to a specific value across several tables in a SQL Server Database. In this case the tables only share values through correlation. I am returning the values I want but the COUNT is counting everything in a given project not just the ones linked to their work items.</p>
  
  <pre><code>        SELECT 
              [d].[Id]
              ,COUNT([t].[ItemId]) AS ItemCount
              ,[d].[ItemName]
          FROM
            [dbo].[Project_Map] [rm] WITH (NOLOCK)
          INNER JOIN
            [dbo].[WorkProjects] [r] WITH (NOLOCK)
              ON  [r].[DomainId] = [rm].[DomainId]
              AND [r].[ProjectId] = [rm].[ProjectId]
              AND [r].[ReleaseId] = [rm].[ReleaseId]
          INNER JOIN
            [dbo].[Items] [d] WITH (NOLOCK)
              ON  [d].[DomainId] = [r].[DomainId]
              AND [d].[ProjectId] = [r].[ProjectId]
              AND [d].[ReleaseId] = [r].[ReleaseId]
          INNER JOIN [dbo].[Projects] [p] with (NOLOCK)
          ON r.DomainId = p.DomainId
          AND r.ProjectId = p.ProjectId
          INNER JOIN [dbo].[Tests] [t] with (NOLOCK)
          ON p.DomainId = t.DomainId
          AND p.ProjectId = t.ProjectId
          INNER JOIN 
          (
              SELECT [Id], MAX([LastModifiedDate]) AS MostRecent
              FROM Items 
              Group By [Id]
          ) AS updatedItem
          ON updatedItem.Id = d.Id
          INNER JOIN
            [dbo].[WorkItemStates] [ds] WITH (NOLOCK)
              ON  [ds].[ItemStateName] = [d].[ItemStatus]
          WHERE 
            d.Id = 111111 
            AND d.UserCategory Like 'SOMESTRING'
          GROUP BY d.Id, d.ItemName
  </code></pre>
  
  <p>RETURNS: In this case the count should be 1 but it returns the count for the entire project.</p>
  
  <pre><code>            ID    COUNT ITEMNAME
              86    5169  SOME NAME
              173   5169  SOME NAME
              170   5169  SOME NAME
  </code></pre>
  
  <p>Am I missing a join somewhere?</p>