I am trying to extract fields from a problem table along with related record counts from a comment table and issue_assigneduser table. If there are 2 comments and 6 assigned users, the values I return are 12 for both values. Any idea how to fix this?
SELECT issue.issueid, COUNT(comment.commentid) AS CountOfComments, Count(issue_assigneduser.userid) as CountOfAssignedUsers, issue.title, issue.detail, issue.enteredby, issue.datetimeentered, issue.assignedto, issue.categoryid, issue.severityid, issue.statusid, issue.lastcommentdatetime as LastCommentDateTime, issue.lastcommentbyuserid, users.initials as LastCommentUserInitials, lookupstatus.status as Status, lookupcategory.category as Category, lookupseverity.severity as Severity, GetUTCDate() as UTCDateTime FROM issue INNER JOIN lookupcategory ON issue.categoryid = lookupcategory.categoryid INNER JOIN lookupseverity ON issue.severityid = lookupseverity.severityid INNER JOIN lookupstatus ON issue.statusid = lookupstatus.statusid LEFT OUTER JOIN comment ON issue.issueid = comment.issueid LEFT OUTER JOIN issue_assigneduser ON issue.issueid = issue_assigneduser.issueid LEFT OUTER JOIN users ON issue.lastcommentbyuserid = users.userid GROUP BY issue.issueid, issue.title, issue.detail, issue.enteredby, issue.datetimeentered, issue.assignedto, issue.categoryid, issue.severityid, issue.statusid, issue.lastcommentdatetime, issue.lastcommentbyuserid, users.initials, lookupstatus.status, lookupcategory.category, lookupseverity.severityid, users.initials, lookupstatus.status, lookupcategory.category, lookupseverity.severity ORDER BY issue.lastcommentdatetime DESC;
source share