Count Distinct Values - Probably Need Of Subquery

[robs23]

Hi Guys,

I've built a kind of user forum in my database and there's a functionality that's supposed to show how many posts ("comments" in my db) has got each thread ("productSteps" in my db) and how many posts are new for current user. So it should say something like:

Thread1 - 10 comments (3 new)

To enable this I have 2 tables (there are more but these ones seem to be important for this thread):

• "tbComments" that contains all comments' content
• "tbCommentsSeen" that contains all users' ids of those who already seen this comment
• "tbProductSteps" that contains all productSteps (think about those as threads for simplicity)

Take a look:



Here's complete SQL:

SELECT Count(tbComments.commId) AS CountOfcommId, tbProductSteps.ProductStepId, Count(IIf([tbCommentSeen].[userId]=whoIsLogged(),1,Null)) AS CommentsSeen, tbProductSteps.ProductId
FROM (tbComments RIGHT JOIN tbProductSteps ON (tbComments.productId = tbProductSteps.ProductId) AND (tbComments.stepId = tbProductSteps.StepId)) LEFT JOIN tbCommentSeen ON tbComments.commId = tbCommentSeen.commId
GROUP BY tbProductSteps.ProductStepId, tbProductSteps.ProductId;

"CountOfcommId" it's supposed to show all comments written to particular thread. Instead, it counts how many times comment was read. If there's only 1 comment but read 4 times it will show 4 instead of 1. I think I need to use subquery to get only distinct values of commId but I have none experience with subqueries and so far I've spent 2 days trying to figure out a solution. I really appreciate any help.

Robert

[NTC]

Well I definitely do not understand your post. So this advice might be off. But counting your tbl Comments per 'thread' might be very easy. It is unclear to me in the table Comments as to which field defines a unique thread (possibly it requires 2 fields) - but in any case the Aggregate query could work.

Create a normal query (query design view) using tblComments only; drag Product Id & Comm Id fields onto the query; put the Product ID on the left. Then change it to an Aggregate query by using the sigma symbol in the ribbon (like a big E) and it will default both to 'Group on'....change the Comm ID to 'Count'. This should give you the idea at least...if you need 2 fields to define a string then drag on the 2nd field and also let it stay as a Group On field.

[InsuranceGuy]

SELECT Count(tbComments.commId) AS CountOfcommId  ,tbProductSteps.ProductStepId
  ,Count(tbComments.commId) - count(unique_Views.viewedCount) as UnreadComments
  ,tbProductSteps.ProductId
FROM tbComments
RIGHT JOIN tbProductSteps ON (tbComments.productId = tbProductSteps.ProductId)
  AND (tbComments.stepId = tbProductSteps.StepId)
LEFT JOIN (
  SELECT commID
    ,userID
    ,1 AS viewedCount
  FROM tbCommentSeen
  WHERE tbComments.commID = tbComments.commID
    AND tbCommentSeen.userID = whoIslogged()
  GROUP BY commID
    ,userID
  ) as unique_Views ON unique_Views.commID = tbComments.commID
GROUP BY tbProductSteps.ProductStepId
  ,tbProductSteps.ProductId;

Freehanding this, but give it a try.