Have they met?

2

Please login or register to vote for this query.

(click on this box to dismiss)

Where have I 'met' another user? Checks for questions, answers, comments and bounties. Note: detecting comment replies rely on text matching and can include false positives.

Stack Overflow

Q&A for professional and enthusiast programmers

-- Have they met?
-- Where have two users met; answer each other's question, commenting on posts or replying, or both competing on a question
-- May throw up some false positives in reply comments because of @Name matches.

declare @AId int = ##UserAId##;
declare @BId int = ##UserBId##;

declare @AName nvarchar(40)
declare @LikeAName nvarchar(40)
select @AName = DisplayName from Users where Id = @AId
select @LikeAName = '%@' + LOWER(REPLACE(@AName, ' ', '')) + '%'

declare @BName nvarchar(40)
declare @LikeBName nvarchar(40)
select @BName = DisplayName from Users where Id = @BId
select @LikeBName = '%@' + LOWER(REPLACE(@BName, ' ', '')) + '%'

-- Question/Answer meetings
  select
    Questions.Id as [Post Link],  
    case
      when Questions.OwnerUserId = @AId then
        FORMATMESSAGE('%s''s question, %s''s answer', @AName, @BName)
      else
        FORMATMESSAGE('%s''s question, %s''s answer', @BName, @AName)
    end as [What],
    case
      when Questions.OwnerUserId = @AId then
        Questions.CreationDate
      else
        Answers.CreationDate
    end as [Date A],
    case
      when Answers.OwnerUserId = @AId then
        Questions.CreationDate
      else
        Answers.CreationDate
    end as [Date B]
  from Posts as Questions
  inner join Posts as Answers
    on Questions.Id = Answers.ParentId
  where Answers.PostTypeId = 2 and Questions.PostTypeId = 1
  and (
        (Questions.OwnerUserId = @AId and Answers.OwnerUserId = @BId)
     or (Questions.OwnerUserid = @BId and Answers.OwnerUserId = @AId)
  )

union

-- Comments on posts
  select 
    Posts.Id as [Post Link],
    case
      when Posts.PostTypeId = 1 and Posts.OwnerUserId = @AId then
        FORMATMESSAGE('%s''s question, %s''s comment', @AName, @BName)
      when Posts.PostTypeId = 1 and Posts.OwnerUserId = @BId then
        FORMATMESSAGE('%s''s question, %s''s comment', @BName, @AName)
      when Posts.PostTypeId = 2 and Posts.OwnerUserId = @AID then
        FORMATMESSAGE('%s''s answer, %s''s comment', @AName, @BName)
      when Posts.PostTypeId = 2 and Posts.OwnerUserId = @BId then
        FORMATMESSAGE('%s''s answer, %s''s comment', @BName, @AName)
    end as [What],
    case
      when Posts.OwnerUserId = @AId then
        Posts.CreationDate
      else
        Comments.CreationDate
    end as [Date A],
    case
      when Comments.UserId = @AId then
        Posts.CreationDate
      else
        Comments.CreationDate
    end as [Date B]
  from Posts
  inner join Comments
    on Posts.Id = Comments.PostId
  where (
        (Posts.OwnerUserId = @AId and Comments.UserId = @BId)
     or (Posts.OwnerUserid = @BId and Comments.UserId = @AId)
  )

union

-- Reply comments
  select
    -- No easy way to generate comment links from SEDE? So link to post instead
    Posts.Id as [Post Link],
    case
      when Comments.UserId = @AId then
        FORMATMESSAGE('%s''s reply to %s''s comment', @AName, @BName)
      when Comments.UserId = @BId then
        FORMATMESSAGE('%s''s reply to %s''s comment', @BName, @AName)
    end as [What],
    -- can't know the date of the comment they addressed
    -- so the following are the date of the post the comment is attached to
    -- and the date of the replying comment
    case
      when Comments.UserId = @AId then
        Comments.CreationDate
      else
        Posts.CreationDate
    end as [Date A],
    case
      when Comments.UserId = @AId then
        Posts.CreationDate
      else
        Comments.CreationDate
    end as [Date B]
  from Comments
    inner join Posts on Comments.PostId = Posts.Id
  where (
       (UserId = @AId and LOWER(Text) like @LikeBName)
    or (UserId = @BId and LOWER(Text) like @LikeAName)
  )
     
union
 -- Both answered the same question
  select
    Question.Id as [Post Link],
    'Both answered the same question' as [What],
    Answer1.CreationDate as [Date A],
    Answer2.CreationDate as [Date B]
  from Posts as Question
  inner join Posts as Answer1
   on Question.Id = Answer1.ParentId
  inner join Posts as Answer2
   on Question.Id = Answer2.ParentId
  where
       Question.OwnerUserId NOT IN (@AId, @BId)
   and Answer1.OwnerUserId = @AId
   and Answer2.OwnerUserId = @BId

union
 -- Bounties on questions
  select
    Question.Id as [Post Link],
    case
      when Bounty.UserId = @AId then
        FORMATMESSAGE('%s''s bounty on %s''s question', @AName, @BName)
      when Bounty.UserId = @BId then
        FORMATMESSAGE('%s''s bounty on %s''s question', @BName, @AName)
    end as [What],
    Question.CreationDate as [Date A],
    Bounty.CreationDate as [Date B]
  from Posts as Question
  inner join Votes as Bounty
    on Question.id = Bounty.PostId
  where
       Question.PostTypeId = 1
   and Bounty.VoteTypeId = 8
   and (
        (Question.OwnerUserId = @AId and Bounty.UserId = @BId)
     or (Question.OwnerUserid = @BId and Bounty.UserId = @AId)
   )

union
 -- Bounties on questions the other user answered
  select
    Answer.Id as [Post Link],
    case
      when Bounty.UserId = @AId then
        FORMATMESSAGE('%s''s bounty on a question that %s answered', @AName, @BName)
      when Bounty.UserId = @BId then
        FORMATMESSAGE('%s''s bounty on a question that %s answered', @BName, @AName)
    end as [What],
    Answer.CreationDate as [Date A],
    Bounty.CreationDate as [Date B]
  from Posts as Question
  inner join Posts as Answer
    on Question.id = Answer.ParentId
  inner join Votes as Bounty
    on Question.id = Bounty.PostId
  where
       Question.PostTypeId = 1
   and Answer.PostTypeId = 2
   and Bounty.VoteTypeId = 8
   and (
        (Answer.OwnerUserId = @AId and Bounty.UserId = @BId)
     or (Answer.OwnerUserid = @BId and Bounty.UserId = @AId)
   )

order by [Date A], [Date B]

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results