Q&A for electronics and electrical engineering professionals, students, and enthusiasts
declare @userid int set @userid = ##UserId##; -- all comments on user's posts or any other post the user commented on. with ccomments as ( select c.* from ( select distinct c.postid from comments c inner join posts p on p.id = c.postid where p.owneruserid = @userid or c.userid = @userid ) x inner join comments c on c.postid = x.postid ), -- all users that participated in each post's comment discussion cusers as ( select distinct c.postid, c.userid, coalesce(u.displayname, c.userdisplayname) displayname, concat('@', lower(replace(coalesce(u.displayname, c.userdisplayname), ' ', ''))) pingname from ccomments c left outer join users u on u.id = c.userid ), -- all users pinged in each posts comment discussion pinged as ( select c.*, lower(substring(c.text, charindex('@', c.text), 100)) pinged from ccomments c where text like '%@%' ) -- now find pings that don't match any existing participant's user names. -- they'll be the ones with NULL u columns. select p.id [Comment Link], p.postid, p.pinged, u.userid, u.displayname, u.pingname from pinged p left outer join cusers u on p.postid = u.postid and p.pinged like concat(u.pingname, '%') order by iif(u.pingname is null, 0, 1), p.pinged