40

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 members of the workforce navigating the professional setting

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

declare @MyId int = ##MyId##;
declare @TheirId int = ##TheirId##;

declare @LikeMyName nvarchar(40)
select @LikeMyName = '%' + DisplayName + '%' from Users where Id = @MyId

declare @TheirName nvarchar(40)
declare @LikeTheirName nvarchar(40)
select @TheirName = DisplayName from Users where Id = @TheirId
select @LikeTheirName = '%' + @TheirName + '%'

-- Question/Answer meetings
  select
   Questions.Id as [Post Link],  
    case
      when Questions.OwnerUserId = @TheirId then @TheirName + '''s question, my answer'
    else 'My question, ' + @TheirName + '''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 = @TheirId and Answers.OwnerUserId = @MyId )
     or (Questions.OwnerUserid = @MyId and Answers.OwnerUserId = @TheirId ))
union
  -- Comments on owned posts
  select Posts.Id as [Post Link],
    case
      when Posts.PostTypeId = 1 and Posts.OwnerUserId = @TheirId then @TheirName + '''s question, my comment'
      when Posts.PostTypeId = 1 and Posts.OwnerUserId = @MyId then 'My question, ' + @TheirName + '''s comment'
      when Posts.PostTypeId = 2 and Posts.OwnerUserId = @TheirId then @TheirName + '''s answer, my comment'
      when Posts.PostTypeId = 2 and Posts.OwnerUserId = @MyId then 'My answer, ' + @TheirName + '''s comment'
    end as [What]  
  from Posts
  inner join Comments
    on Posts.Id = Comments.PostId
  where ((Posts.OwnerUserId = @TheirId and Comments.UserId = @MyId )
     or (Posts.OwnerUserid = @MyId and Comments.UserId = @TheirId ))

union
 -- @comments on posts
  select Posts.Id as [Post Link],
    case
      when UserId = @TheirId then @TheirName + '''s reply to my comment'
      when UserId = @MyId then 'My reply to ' + @TheirName + '''s comment'
    end as [What]  
  from Comments
    inner join Posts on Comments.PostId = Posts.Id
  where ((UserId = @TheirId and Text like @LikeMyName )
     or (UserId = @MyId and Text like @LikeTheirName))

order by [Post Link]

fork query

created jan 26 11 Benjol