Encyclopedia - Reports - Kanji staff tasks - possible translations
Illegal mix of collations (utf8mb4_bin,NONE), (utf8mb4_uca1400_ai_ci,COERCIBLE), (utf8mb4_uca1400_ai_ci,COERCIBLE) for operation 'replace'
SELECT t.id AS row_id, t.count_JP AS nb_times_used, t.id, t.title AS `title `, CONCAT_WS( ' / ', GROUP_CONCAT( DISTINCT CONCAT( IF(a.variation=2 OR b.variation=2 OR c.variation=2 OR d.variation=2, '<font color=blue>', ''), IF(p.id=IFNULL(trui.id,tr.id), '<b>', ''), REPLACE( CASE WHEN CONCAT_WS('',a.kanji,b.kanji,c.kanji,d.kanji) <> t.title THEN NULL WHEN tr.id IS NULL THEN IF(CONCAT_WS('',a.variation,b.variation,c.variation,d.variation)=0, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT_WS(' ',a.english,b.english,c.english,d.english),'¹',''),'²',''),' ',' '),'Animation Animation','Animation'),'- ','-'), NULL) WHEN trui.id IS NULL THEN CONCAT(tr.title,' (',tr.id,')') ELSE CONCAT(trui.title,' (',trui.id,')') END ,' ',' '), IF(p.id=IFNULL(trui.id,tr.id), '</b>', ''), IF(a.variation=2 OR b.variation=2 OR c.variation=2 OR d.variation=2, '</font>', '') ) ORDER BY IF(CONCAT_WS('',a.kanji,b.kanji,c.kanji,d.kanji)=t.title, IF(trui.id, trui.count_JP, tr.count_JP), NULL) DESC SEPARATOR ' / ' ), IF(MAX(IF(CONCAT_WS('',a.kanji,b.kanji,c.kanji,d.kanji)=t.title, IF(trui.id, trui.id, tr.id), NULL)=p.id), NULL, CONCAT('<b><i>', p.title,' (',p.id,')</i></b>') ) ) AS possible, #group=translations COUNT(DISTINCT IFNULL(trui.id,tr.id)) AS existing_english_tasks #group=translations FROM annAnimeStaffTask AS t#kanji task LEFT JOIN annAnimeStaffTask AS p ON t.task_to_use_instead = p.id LEFT JOIN annKanjiTranslation a ON t.title LIKE CONCAT(a.kanji,'%') LEFT JOIN annKanjiTranslation b ON t.title LIKE CONCAT(a.kanji,b.kanji,'%') LEFT JOIN annKanjiTranslation c ON t.title LIKE CONCAT(a.kanji,b.kanji,c.kanji,'%') LEFT JOIN annKanjiTranslation d ON t.title LIKE CONCAT(a.kanji,b.kanji,c.kanji,d.kanji,'') LEFT JOIN annAnimeStaffTask tr ON REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT_WS(' ',a.english,b.english,c.english,d.english),'¹',''),'²',''),' ',' '),'Animation Animation','Animation'),'- ','-') = tr.title AND CONCAT_WS('',a.kanji,b.kanji,c.kanji,d.kanji) = t.title LEFT JOIN annAnimeStaffTask trui ON tr.task_to_use_instead = trui.id WHERE LENGTH(t.title) / CHAR_LENGTH(t.title) > 1.8 GROUP BY t.id ORDER BY p.title, MAX(CONCAT_WS('',a.kanji,b.kanji,c.kanji,d.kanji)=t.title), t.count_JP DESC, t.title /*html: <style> table tr.location_hash td{ background-color: #ddf } </style> */