1

Please login or register to vote for this query.

(click on this box to dismiss)

Identifies story-id questions where the OP has commented positively on a non-accepted answer. Might be a duplicate candidate if they've 'verbally accepted'.

Q&A for professional and enthusiast programmers

SELECT DISTINCT
  Comments.Id AS [Comment Link],
  Comments.UserId AS [User Link],
  Answer.Id AS [Post Link]
FROM Comments
  INNER JOIN Posts Answer
    ON Comments.PostId = Answer.Id
  INNER JOIN Posts Question
    ON Answer.ParentId = Question.Id
      /* Comment was made by OP */
      AND Comments.UserId = Question.OwnerUserId
  INNER JOIN PostTags
    ON Answer.ParentId = PostTags.PostId
  INNER JOIN Tags
    ON PostTags.TagId = Tags.Id
WHERE
  /* Looking only for 'story-ident' */
  Tags.Id = 130
  /* Question has no accepted answer */
  AND Question.AcceptedAnswerId IS NULL
  /* Answer has 'thank you' type comment from OP */
  AND 
  (
    LOWER(Comments.Text) LIKE LOWER('%thank%') OR
    LOWER(Comments.Text) LIKE LOWER('%yes%') OR
    LOWER(Comments.Text) LIKE LOWER('%helped%') OR
    LOWER(Comments.Text) LIKE LOWER('%this%is%it%') OR
    LOWER(Comments.Text) LIKE LOWER('%been%looking%') OR
    LOWER(Comments.Text) LIKE LOWER('%that_s%it%')
   )
   AND
   (
     LOWER(Comments.Text) NOT LIKE LOWER('%not%') AND
     LOWER(Comments.Text) NOT LIKE LOWER('%sorry%') AND
     LOWER(Comments.Text) NOT LIKE LOWER('%nope%')
   )
  /* But answer is not accepted */
  AND Question.AcceptedAnswerId <> Answer.Id
ORDER BY Comments.Id;

fork query

edited nov 20 17

created nov 9 17 Mooz