1

Please login or register to vote for this query.

(click on this box to dismiss)

For the list of non-series tags, see: https://data.stackexchange.com/anime/query/1863699.

Q&A for scientists using computers to solve scientific problems

WITH NonSeriesTags AS (
    SELECT 
          Tags.TagName
        , TagSynonyms.SourceTagName 
    FROM Tags
    LEFT JOIN TagSynonyms
        ON Tags.TagName LIKE '%' + TagSynonyms.TargetTagName + '%'
    WHERE Tags.TagName IN (
          'aimer'
        , 'barasui'
        , 'hayao-miyazaki'
        , 'hideaki-anno'
        , 'hiroyuki-sawano'
        , 'jun-maeda'
        , 'kajiura-yuki'
        , 'kazuki-nakashima'
        , 'kazuki-takahashi'
        , 'ken-akamatsu'
        , 'kouji-seo'
        , 'maaya-sakamoto'
        , 'makoto-shinkai'
        , 'mamiko-noto'
        , 'mamoru-hosoda'
        , 'mamoru-oshii'
        , 'masamune-shirow'
        , 'sachika-misawa'
        , 'shun-saeki'
        , 'domo-kun'
        , 'hatsune-miku'
        , 'os-tan'
        , 'super-sonico'
        , 'crunchyroll'
        , 'funimation'
        , 'gainax'
        , 'key'
        , 'kyoto-animation'
        , 'madhouse'
        , 'netflix'
        , 'sentai-filmworks'
        , 'studio-ghibli'
        , 'type-moon'
        , 'australia'
        , 'england'
        , 'europe'
        , 'germany'
        , 'greece'
        , 'india'
        , 'japan'
        , 'north-korea'
        , 'singapore'
        , 'spain'
        , 'united-states'
        , 'anime-expo'
        , 'anime-no-chikara'
        , 'anime-planet'
        , 'claris'
        , 'comiket'
        , 'myanimelist'
        , 'pixiv'
        , 'ribon'
        , 'shonen-jump'
        , 'vocaloid'
    )
)
SELECT
      Posts.Id AS [Post Link]
    , Posts.Tags
    , NonSeriesTags.TagName AS [Potential Missing Tags]
    , NonSeriesTags.SourceTagName AS [Tag Synonym]
FROM Posts
INNER JOIN NonSeriesTags
    ON Posts.PostTypeId = 1
    AND Posts.Tags NOT LIKE '%' + NonSeriesTags.TagName + '%'
WHERE
    LOWER(Posts.Title) LIKE '%[^a-zA-Z]' 
        + REPLACE(NonSeriesTags.TagName, '-', ' ') 
        + '[^a-zA-Z]%' 
        OR LOWER(Posts.Title) LIKE '%[^a-zA-Z]' 
            + REPLACE(NonSeriesTags.SourceTagName, '-', ' ') 
            + '[^a-zA-Z]%' 
ORDER BY 
      NonSeriesTags.TagName
    , Posts.Id

view current version

edited oct 1

created oct 1 Gao