0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for gardeners and landscapers

WITH FilteredPosts AS (
    SELECT p.Id
    FROM Posts p
    WHERE
    (
        p.Body LIKE '%cloud%'
        OR p.Body LIKE '%cloud computing%'
    )
    AND (
        p.Body LIKE '%legacy%'
        OR p.Body LIKE '%on-premise%'
        OR p.Body LIKE '%on premise%'
        OR p.Body LIKE '%on demand%'
        OR p.Body LIKE '%old system%'
    )
    AND (
        p.Body LIKE '%migration%'
        OR p.Body LIKE '%shift%'
        OR p.Body LIKE '%adoption%'
    )
),
FilteredWikiPosts AS (
    SELECT p.Id
    FROM Posts p
    WHERE p.PostTypeId IN (4, 5)  -- Tag Wiki Excerpt and Tag Wiki
    AND (
        p.Body LIKE '%cloud%'
        OR p.Body LIKE '%cloud computing%'
    )
    AND (
        p.Body LIKE '%legacy%'
        OR p.Body LIKE '%on-premise%'
        OR p.Body LIKE '%on premise%'
        OR p.Body LIKE '%on demand%'
        OR p.Body LIKE '%old system%'
    )
    AND (
        p.Body LIKE '%migration%'
        OR p.Body LIKE '%shift%'
        OR p.Body LIKE '%adoption%'
    )
)
SELECT DISTINCT t.Id, t.TagName
FROM Tags t
JOIN PostTags pt ON pt.TagId = t.Id
JOIN FilteredPosts fp ON fp.Id = pt.PostId
WHERE EXISTS (
    SELECT 1
    FROM FilteredWikiPosts fwp
    WHERE fwp.Id = t.WikiPostId OR fwp.Id = t.ExcerptPostId
)
AND (
    t.TagName LIKE '%cloud%'
    OR t.TagName LIKE '%cloud computing%'
)
AND (
    t.TagName LIKE '%legacy%'
    OR t.TagName LIKE '%on-prem%'
    OR t.TagName LIKE '%on prem%'
    OR t.TagName LIKE '%on demand%'
    OR t.TagName LIKE '%old system%'
)
AND (
    t.TagName LIKE '%migration%'
    OR t.TagName LIKE '%shift%'
    OR t.TagName LIKE '%adoption%'
);

fork query

edited may 18

created may 18 anonymous