0

Please login or register to vote for this query.

(click on this box to dismiss)

Where have I 'met' another user? (Reminder, Jon Skeet's user id is 22656 :) May throw up some false positives because of @Name matches.

Q&A about the site for scientific skepticism

-- 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
       @AName + '''s question, ' + @BName + '''s answer'
     else
       @BName + '''s question, ' + @AName + '''s answer'
   end as [What]
  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
        @AName + '''s question, ' + @BName + '''s comment'
      when Posts.PostTypeId = 1 and Posts.OwnerUserId = @BId then
        @BName + '''s question, ' + @AName + '''s comment'
      when Posts.PostTypeId = 2 and Posts.OwnerUserId = @AID then
        @AName + '''s answer, ' + @BName + '''s comment'
      when Posts.PostTypeId = 2 and Posts.OwnerUserId = @BId then
        @BName + '''s answer, ' + @AName + '''s comment'
    end as [What]  
  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
    Posts.Id as [Post Link],
    case
      when UserId = @AId then
        @AName + '''s reply to ' + @BName + '''s comment'
      when UserId = @BId then
        @BName + '''s reply to ' + @AName + '''s comment'
    end as [What]  
  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]
  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

order by [Post Link]

fork query

edited jul 20 18 parseraisin

created jan 26 11 Benjol