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]