Find missing shell tags

0

Please login or register to vote for this query.

(click on this box to dismiss)

Craft CMS Meta

Q&A about the site for administrators, end users, developers and designers for Craft CMS

-- Find posts tagged with specific shell, but not with shell.

SELECT TOP 200 p.Title,
               'https://codereview.stackexchange.com/questions/' + CAST(p.id AS VARCHAR) as URL,
               p.Tags,
               p.CreationDate
FROM   posts p
CROSS APPLY (
       SELECT t.TagName TagSpecific
       FROM tags t
       INNER JOIN posttags pt on pt.tagid = t.id
       WHERE pt.postid = p.id
       AND t.TagName in ('powershell', 'bash')
) t_specific
OUTER APPLY (
       SELECT t.TagName TagGeneric
       FROM tags t
       INNER JOIN posttags pt on pt.tagid = t.id
       WHERE pt.postid = p.id
       AND t.TagName = 'shell'
) t_generic
WHERE  1 = 1
AND    TagGeneric is NULL
AND    p.DeletionDate is NULL
AND    p.Body is NOT NULL
AND    p.ClosedDate is NULL
AND    ((SELECT count(*) FROM PostTags WHERE PostId = p.Id) < 5)
ORDER  BY p.CreationDate DESC
;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results