Comments exchanged between two users

1

Please login or register to vote for this query.

(click on this box to dismiss)

Find comments exchanged between two users. This finds all comments that user1 left on user2's posts and vice versa. This also attempts to find comments on third-party posts with @ mentions, but this is only approximative: it's based on users' current display names rather than the name at time of posting, it includes @ mentions that actually target another user with a similar name, it misses @ mentions that use a prefix of the name, and it misses the case where a user replies to the sole comment under their own post. It's difficult to do better considering the complex rules surrounding comment notification targets.

Stack Overflow

Q&A for professional and enthusiast programmers

declare @user1_name nvarchar(40)
set @user1_name = (select top 1 replace(lower(DisplayName), ' ', '') from Users where Id = ##User1##)
declare @user2_name nvarchar(40)
set @user2_name = (select top 1 replace(lower(DisplayName), ' ', '') from Users where Id = ##User2##)

select
  c.Id as [Comment Link],
  c.CreationDate as [Date],
  c.Score as [Score],
  c.UserId as [User Link]
from Comments c
left join Posts p on p.Id = c.PostId
where (c.UserId = ##User1## and
       (p.OwnerUserId = ##User2## or replace(lower(c.text), ' ', '') like '%@' + @user2_name + '%'))
   or (c.UserId = ##User2## and
       (p.OwnerUserId = ##User1## or replace(lower(c.text), ' ', '') like '%@' + @user1_name + '%'))
order by c.CreationDate asc

Enter Parameters

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