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 tagnames as (
select tagsynonyms.* from tags
inner join tagsynonyms
on tags.tagname like '%' + tagsynonyms.targettagname + '%'
and tagsynonyms.targettagname 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 * from posts
inner join tagnames
on posts.posttypeid = 1
and posts.tags like '%' + tagnames.targettagname + '%'
where 
(lower(posts.title) not like replace(tagnames.sourcetagname, '-', ' ') or posts.title not like replace(tagnames.targettagname, '-', ' '))
or (lower(posts.body) not like replace(tagnames.sourcetagname, '-', ' ') or posts.body not like replace(tagnames.targettagname, '-', ' '))
/*
--or posts.body like any (select '%' + sourcetagname + '%' from tagnames union select '%' + targettagname + '%' from tagnames))
--and posts.tags not like any (select '%' + sourcetagname + '%' from tagnames union select '%' + targettagname + '%' from tagnames)
*/

view current version

edited oct 1

created oct 1 Gao