count how many tasks under project is open or onhold for logged in user

[a-elnajjar]

I want to created nav menu to show Project name and number of task is open or onhold for associated logged in tech
the result should be like this my problem with is my script give me total number of task under project
for tech1 and his associate task are open or onhold
hello World 1(0)
hello World 2(3)
hello World 3(1)
hello World 4(2)

SELECT Projects.projectID,
    Projects.projectName + ' ' + '(' + CONVERT(NVARCHAR, COUNT(Projects_tasks.taskID)) + ')' AS Project
  FROM Projects
  LEFT JOIN Projects_tasks ON Projects.projectID = Projects_tasks.projectID
 INNER JOIN Users_projects ON Projects.projectID = Users_projects.projectID
 INNER JOIN Tasks ON Projects_tasks.taskID = Tasks.taskID
 INNER JOIN Users_Tasks ON Tasks.taskID = Users_Tasks.TaskID
 WHERE Users_Tasks.userID = @userID
   AND (Tasks.status = 'Open' OR Tasks.status = 'Onhold')
 --also this code can be used: 
 --AND Tasks.status in ('Open', 'Onhold')
 GROUP BY Users_projects.userID,
          Projects.projectName,
          Projects.projectID,
          Users_Tasks.userID

Capture.PNG

[K. Wolfe]

All your joins should be inner. There's no need to do a conversion on something that will be counted. Other than that I see nothing else wrong?