username stalker

0

Please login or register to vote for this query.

(click on this box to dismiss)

Gardening and Landscaping Meta

Q&A about the site for gardeners and landscapers

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

Enter Parameters

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