0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for administrators, end users, developers and designers for Craft CMS

SELECT TOP 360
/* Need to look at multiple tables unioned together; that outcome is called TableEntry */ 
  TableEntry.Id as [Post Link],
  TableEntry.OwnerUserId as [User Link],
  Sum(TableEntry.SelfAnswers) as SelfAnswers,
  Sum(TableEntry.SelfComments) as SelfComments,
  Sum(TableEntry.NonSelfAnswers) as NonSelfAnswers,
  Sum(TableEntry.NonSelfComments) as NonSelfComments
FROM (
  SELECT /* table for selecting posts where the owner self-answered */
    q.Id as Id,
    q.OwnerUserId as OwnerUserId,
    Count(p.Id) as SelfAnswers, 
    0 as SelfComments, /* must have matching columns so 0 for comments column */
    0 as NonSelfAnswers,
    0 as NonSelfComments
  FROM Posts q
    INNER JOIN Posts p 
      ON p.ParentId = q.Id
      AND p.OwnerUserId = q.OwnerUserId
  WHERE   
    p.OwnerUserID = q.OwnerUserId AND
    q.Score >= 0 AND
    p.Score > 0
  GROUP BY q.Id, q.OwnerUserId /* removes duplicates by collapsing the rows */
  UNION /* to combine the two queries */
  SELECT /* table for selecting comments from the post's owner */
    q.Id as Id,
    q.OwnerUserId as OwnerUserId,
    0 as SelfAnswers, /* ditto for matching columns */
    Count(c.Id) as SelfComments,
    0 as NonSelfAnswers,
    0 as NonSelfComments
  FROM Posts q
    INNER JOIN  Comments c
        ON c.PostId = q.Id
        AND c.UserId = q.OwnerUserId
  WHERE   
    q.Score >= 0 AND
    q.PostTypeId = 1 /* we only want q to be question posts; 
                        if this is not how you imagined self-comments, edit! :)  */
  GROUP BY q.Id, q.OwnerUserId
  UNION
  SELECT /* table for selecting posts where the owner self-answered */
    q.Id as Id,
    q.OwnerUserId as OwnerUserId,
    0 as SelfAnswers, 
    0 as SelfComments, /* must have matching columns so 0 for comments column */
    Count(p.Id) as NonSelfAnswers,
    0 as NonSelfComments
  FROM Posts q
    INNER JOIN Posts p 
      ON p.ParentId = q.Id
      AND p.OwnerUserId != q.OwnerUserId
  WHERE   
    q.Score >= 0 AND
    p.Score > 0
  GROUP BY q.Id, q.OwnerUserId /* removes duplicates by collapsing the rows */
  UNION
  SELECT /* table for selecting comments from the post's owner */
    q.Id as Id,
    q.OwnerUserId as OwnerUserId,
    0 as SelfAnswers, /* ditto for matching columns */
    0 as SelfComments,
    0 as NonSelfAnswers,
    Count(c.Id) as NonSelfComments
  FROM Posts q
    INNER JOIN  Comments c
        ON c.PostId = q.Id
        AND c.UserId != q.OwnerUserId
  WHERE   
    q.Score >= 0 AND
    q.PostTypeId = 1 /* we only want q to be question posts; 
                        if this is not how you imagined self-comments, edit! :)  */
  GROUP BY q.Id, q.OwnerUserId
  
  ) AS TableEntry
GROUP BY TableEntry.Id, TableEntry.OwnerUserId /* removes duplicates by collapsing the 
                                                  rows and allows aggregation */
Order BY SelfAnswers DESC

fork query

edited oct 1

created oct 1 anonymous