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 gardeners and landscapers
-- 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]