0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for system and network administrators

SELECT
  q.Id AS [Post Link],
  Tags.TagName AS 'Ghost Tag',
  ph.Text AS 'Actual Tag'
from
  Posts q
    INNER JOIN PostTags ON q.Id = PostTags.PostId
    INNER JOIN Tags ON PostTags.TagId = Tags.id
    INNER JOIN (
      SELECT PostId, MAX(id) AS MaxId
      FROM PostHistory
      WHERE PostHistoryTypeId IN (3, 6, 9)
      GROUP BY PostId
    ) AS phx ON phx.PostId = q.Id
    INNER JOIN PostHistory AS ph ON phx.MaxId = ph.Id
WHERE
  -- check if tag included in revision history
  CHARINDEX('<' + Tags.TagName + '>', ph.Text) = 0 AND
  -- Single tag handling
  Tags.TagName <> ph.Text AND
  q.PostTypeId = 1
ORDER BY
  q.LastActivityDate

view current version

edited may 30 15

created may 30 15 masegaloeh