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